macro to sum like cells across multiple worksheets

shammer

New Member
Joined
Feb 26, 2002
Messages
25
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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top