1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel: Add up a certain cell across multiple files

Discussion in 'Business Applications' started by mickylaren, Feb 23, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. mickylaren

    mickylaren Thread Starter

    Joined:
    Jan 29, 2004
    Messages:
    58
    I have 30 excel files formatted identically, and I want to create a new file containing a cell with the sum of all numbers in a specific cell (let's say G12) from all of those other files (they're all located in one folder). Is there a way to do this?

    By the way, each of the 30 files has five worksheets, but the cell I'm talking about is on sheet 1.

    Thanks for any help you can provide!

    -Micky
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    This can be done with a macro. Are you familiar with using macros in Excel?

    Rollin
     
  3. alpac

    alpac

    Joined:
    Feb 18, 2004
    Messages:
    49
    I know I did not ask the question here but I would like to know how to do Macros in excel. I would like to learn how macros are done in Word, Excel and other Microsoft Applications.
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    The easiest way to learn how to do macros in these applications is to use the built in macro recorders to do common tasks and then look at the code and learn what each line does. After learning the basics you can find many useful resources on the internet. Just do a search for the term VBA and you'll get tons of hits.

    Rollin
     
  5. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Micky, you didn't say if you needed a one time number or a working cell in a new file.

    If you want a New File that sums from other 30 files then merely open the files and create your formula selecting what you want.
    (or if your filenames are pretty straight forward just create the formula you need. That saves all the jumping around. Example =[FORD.xls]Sheet1!$A$1+[CHEVY.xls]Sheet1!$A$1+[HONDA.xls]Sheet1!$A$1 It's easy to cut and paste it.

    If any of the other 30 files is changed later, you will be asked by your new file if you wish it to UPDATE the results when you open it.

    - Castleheart


    and Alpac - Rollin said it ....just create simple MACROS from the tools menu.
    Hit ALT F8 to run them - or better - use the FORMS toolbar from VIEW to create buttons to assign them too.
    Then when you're through hit ALT F11 to view what you have created. Start with simple formulas and then work up to format changes and later to heavy duty codes. You grow with your needs. (y)
     
  6. mickylaren

    mickylaren Thread Starter

    Joined:
    Jan 29, 2004
    Messages:
    58
    Hey - thanks for the responses.
    I want to have a file that constantly updates itself depending on what the other files say. Is it a problem if some of the files are in different folders? In your formula, it only contains file names (no folders).

    How would I go about creating a macro that does this?
     
  7. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    No problemo ! It can even be on another networked computer.

    An example formula could look something like this:

    ='C:\Documents and Settings\Micky\Desktop\GoodCars\[FORD.xls]F150'!$A$4+'\\Micky'sMom\Stuff\Things\Bowties\[CHEVY.xls]Vega'!$A$8

    The cell containing this long looking formula would be adding up two cells:

    cell A4, found on your desktop in a folder called GOODCARS in the spreadsheet called FORD in the worksheet called F150

    - and -

    cell A8 found on your mother's computer named Micky'sMOM under stuff and things in a folder called BOWTIES and a spreadsheet named CHEVY under the tab called VEGA

    You wouldn't! It's not a macro ... it's a formula. Write it yourself - or have EXCEL write it for you.

    In this example - and perhaps in your case with 30 folders - it certainly would be simplest if they were in the same folder for namings sake. But if they weren't and the formulas were just too confusing to write - you would simply open the spreadsheets and select the cells one by one creating the logical path of your formula and let EXCEL actually write the formula.


    Two things:

    1) If you move or rename a spreadsheet.... guess what.... you have messed up the link. You would be prompted that it couldn't UPDATE LINKS and you would be given the option to EDIT LINKS. You can do this.... but it would be easiest to avoid that situation by seeing that your 30 spreadsheets were named and placed properly from the git go.

    2) I guess I combined both things into one - or else I forgot what #2 is already. Sad..



    - C :rolleyes:

    PS: Lemme know if I created more questions than I answered ! Maybe I can unconfuse you.
     
  8. mickylaren

    mickylaren Thread Starter

    Joined:
    Jan 29, 2004
    Messages:
    58
    Ah - perfect. THat's what I wanted to know.
    I was hoping there was some way to tell excel to "Add up all A1 cells in the files in Folder C:\Documetns and Settings\Micky\My Documents\Excel\Addy"

    I guess I'll just manually link it.
     
  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You can do it using a Macro.

    Here is a sample macro that I wrote that loop through all the files in your directory and creates the formulas for you. So if you were to start the macro in cell A1 on your "Master Workbook" it would loop through all the workbooks in your specified directory and create the formulas for you based on the values of cell A1 in all the source workbooks. If you click in another cell and run the macro it will do the same thing except it will now execute based on the address of the cell you selected. Make sure to set reference in your VBA editor to "Microsoft Scripting Runtime" and also make sure the only files in the directory are your Excel files.

    Code:
    Public Sub test()
    
    Dim Fso As Scripting.FileSystemObject
    Dim fsDir As Scripting.Folder
    Dim fsFile As Scripting.File
    Dim vPath As String
    Dim vTotal as String
    
    Set Fso = New Scripting.FileSystemObjec
    Set fsDir = Fso.GetFolder("C:\Documetns and Settings\Micky\My Documents\Excel\Addy")
    
    
    vColumn = ActiveCell.Column
    vRow = ActiveCell.Row
    
    For Each fsFile In fsDir.Files
    
    vPath = Left(fsFile.Path, Len(fsFile) - Len(fsFile.Name))
    
    vName = fsFile.Name
    
    If vSkip = False Then
    
    vTotal = "=" & "'" & vPath & "[" & vName & "]" & "Sheet1" & "'" & "!" & "R" & vRow & "C" & vColumn
    
    vSkip = True
    
    Else
    
    vTotal = vTotal & "+" & "'" & vPath & "[" & vName & "]" & "Sheet1" & "'" & "!" & "R" & vRow & "C" & vColumn
    
    End If
    
    Next
    
    ActiveCell.Formula = vTotal
    
    
    End Sub
    Rollin
     
  10. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Wow Rollin_A... you cooked up that code awful quick. I'm impressed! (y) I'll have to give it some play later. I'm always up for new ways to do things.



    I was just going to offer the suggestion of linking the A1 cells of all 30 workbooks to the new workbook and then just do a simple formula.

    Example:

    You want cells G1:G30 in you new workbook linked to your 30 A1 cells.
    That is as simple as going to G1 and entering "=" and then selecting the A1 cell of the first of your 30 Workbooks.

    Now G1 has written the formula:
    C:\Documents and Settings\Micky\My Documents\Excel\Addy[whatshisname1.xls]sheet1'!$A$1 and all you had to do was type a "="

    Now grab the fill handle of that cell and drag it down to G30. That copies the formula in all 30 cells.

    then you can go cell by cell down column G and change the whatshisname names to correspond to your 30 workbook names. (and if you needed them, you could then select cells G1toG30 and drag their formulas to the right to sum other cells from the 30 sheets. you'll need to remove the appropriate $ signs)

    lastly ... go to the cell you choose and enter =SUM(G1:G30)

    DONE!


    - C
     
  11. MSM Hobbes

    MSM Hobbes

    Joined:
    Apr 22, 2004
    Messages:
    6,554
    Rollin & Castle Heart... (y) ;)
     
  12. mickylaren

    mickylaren Thread Starter

    Joined:
    Jan 29, 2004
    Messages:
    58
    Castleheart, that's an interesting solution. That may be the best option, but I'd prefer something more automated.

    Rollin_Again, thanks a million for that response. I apologize for my ignorance (I've never programmed), but where would I actually enter that code (i.e., I'm looking at a blank workbook. Where does it go?) Also, since I can't understand code, where do I specify the directory containing all the files?

    Thanks again!

    -Micky :-D
     
  13. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Change the following line of the code to reflect the true path to your directory of files.

    Code:
    Set fsDir = Fso.GetFolder("C:\Documetns and Settings\Micky\My Documents\Excel\Addy")
    Next you should open your main Master Workbook that will contain your totals and then press ALT + F11 to bring up your VB editor.

    First set reference to Microsoft Scripting Runtime. To do this, on the VB Editor click TOOLS --> REFERENCES and place a check by "Microsoft Scripting Runtime" and then click OK. Next resave the workbook by clicking the save icon on the VB editor.

    Next, insert a module by selecting INSERT--> MODULE. Once the blank module is inserted, copy and paste the code I provided into the blank module and re-save the workbook.

    One more thing, what are the names of the sheets in your source workbooks? My code uses "Sheet1" as the sheetname containing your values to add. If you want to change it, just replace the word "Sheet1" in my code to the sheet's true name.

    After all this has been done, you can simply select a cell and click ALT + F8 to run the code or select TOOLS --> MACROS from the main Excel workbook window.


    Rollin
     
  14. mickylaren

    mickylaren Thread Starter

    Joined:
    Jan 29, 2004
    Messages:
    58
    When I tried that out, I got an error and it highlighted New Scripting.FileSystemObjec

    Compile error:
    User-defined type not defined.
     
  15. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    got an error and it highlighted New Scripting.FileSystemObject

    probably a typo

    -C
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/333793

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice