macro to sum like cells across multiple worksheets

[shammer]

I am looking to identify and sum values included on multiple worksheets within a file.

Basically column A consists of identifiers (say A-Z). Column F consists of a dollar value. I would like a macro that could search through all worksheets and populate a new worksheet that would sum up all the values in column F for each identifier A-Z and list these identifiers along with the summed values.

I can do this on one worksheet but the files to which I would like to apply this macro consist of approx 30 worksheets.

Can anyone please give me some advice on this one? I would greatly appreciate it!

[Brian]

Can be done with formulas.

Easiest if you add a sheet to perform the summary in the existing workbook, because then you can use the indirect Function.

Assuming each source sheet has identifier in col A and data to sum in col F

Create a table with all of your idetifiers in col A (starting at row #5) and sheet names in row #1.

The function to populate the table would be:
=SUMIF(INDIRECT(B$1&"!$A:$A"),$A5,INDIRECT(B$1&"!$F:$F"))

I suggest adding a check-sum to your table, such as:
In row #2, use similar equation to sum all of the data on each source sheet.
In row #3, sum the data from the table column
In Row #4 = IF (B2<> B3, "ERORR","OK")
In A4 = IF(countif(B2:B256,"ERROR")>0,"ERROR!","OK")

Regards,

Brian

[shammer]

The only problem I have with this is the chance that the worksheets names may change over time (by addition of new worksheets and/or name changes).

This serves my purpose at this time, however; I greatly appreciate your help!

[Brian]

To accomodate sheet name changes:
Instead of manually typing in the sheet names in the top row, create the sheet name using a formula, use:

A1 =CELL("filename",Sheet!1:65536)

To return the full path, filename & sheet
From this you need to extract the sheet by searching for ].

A2 = =RIGHT(A1, LEN(A1)-SEARCH("]",A1) )

or all together:
=RIGHT(CELL("filename",Sheet!1:65536),LEN(CELL("filename",Sheet!1:65536))-SEARCH("]",CELL("filename",Sheet!1:65536)))

Easiest to enter this formula, drag it accross the rows and manually replace the sheet name.

If the sheet name changes, then excel will automatically update it.

To enhance the use of your summary sheet, it is good to add links to the source sheet.

To create a hyperlink to each sheet from then use a formula similar to: (In this case the sheet name is in cell A5, and the text to display is the sheet title in cell B2 on the destination sheet.)

=IF(ISERROR(INDIRECT(A5&"!b2")),"-",HYPERLINK("["&SUBSTITUTE(LEFT(CELL("filename",A5),SEARCH("]",CELL("filename",A5))),"[","",1)&A5&"!A1",INDIRECT(A5&"!b2")))

Place a link back to the summary sheet using:
1) insert a hyperlink manually

2) I used the following to jump back to the relevant row in my summary sheet (in this case the appropriate column) by using the match statement to look up the sheet name. Therefore same formula on every datasheet!

=HYPERLINK("["&SUBSTITUTE(LEFT(CELL("filename",A1),SEARCH("]",CELL("filename",A1))),"[","",1)&"index!A" & MATCH($A$2,Index!$A:$A,0),"{ INDEX }")

Cheers,

Brian