Data from multiple identical sheets

**clacka** · 01-06-2011, 05:43 PM

Greetings!! I have multiple identical worksheets called "Cost worksheet 1, Cost worksheet 2," etc...

Each of the sheets has a list of ingredients and then a total at the bottom. The total is in Cell D25 on each of the sheets.

Is there a way to set up a summary worksheet at the end or beginning that looks like this:

-------------------------]---------------A---------------]-------B----------]
_____1________]__Cost worksheet 1 __]____$3.45__]
_____2________]__Cost worksheet 2___]____$4.57__]
_____3________]__Cost worksheet 3___]____$5.60__]

I have forty sheets and they change from time to time. I'd really be great not to have to manually cut and paste!

Thank you!!

**ChemistB** · 01-06-2011, 05:53 PM

In B1
=INDIRECT("'"&A1&"'!C25") dragged down
That's an initial double quotes around a single quote/apostrophe, also a single quote before the !
Does that work for you?

**clacka** · 01-06-2011, 06:09 PM

Yes!!!

However, the names of the worksheets change from time to time as well as things are updated. Is there a way for column A to contain all of the names of the worksheets in the workbook? That would be great. Then I wouldn't have to cut and paste each worksheet name into the cells in Column A.

Edit: To be more specific, the worksheets are not actually called "Cost Sheet 1, Cost sheet 2, etc." They are more like "Big Chicken 1.2" and "Myan with Mayo" and "Tourist in Rome" They are all sandwich names and the guys in the kitchen costing them out get creative some times!

Thank you so much!!!

**ChemistB** · 01-07-2011, 10:40 AM

VBA is the best way to do that. Copy this macro into a module
(Alt + F11)>Insert > Module then paste.

Please Login or Register  to view this content.

Replace Summary with the name of the worksheet that you want this info moved into
I assume the list starts in A1. Modify if necessary. Does this work for you?