Excel - Adding one cell from multiple worksheets

[mistos]

I have multiple worksheets that are all identical except for data and the worksheet name. I also have a worksheet called Tally. I am looking to take the same cell from every worksheet and copy the data in that cell to the corresponding row in the Tally sheet. The Tally sheet consists of the names of each worksheet. Currently I am going to each row in the tally sheet and typing ='sheet1'!F1 for each row item in the tally sheet corresponding to the named worksheet. Is there any way I can do the first item in the tally and then drag down the the cell, having it take cell F1 from each worksheet and corresponding its similarly named row in the tally. The rows in the tally sheet are in the same order as the worksheets.

 

[JGillespie]

Look at the indirect function. If you are taking cell A1 from each sheet and you have the sheet names listed down a column on your tally sheet, then use the following formula:

=INDIRECT(A1 & "!a1&quot

the first A1 refers to the cell on the tally sheet that contains the sheet name you wantto pull a value from. the second cell reference (a1) refers to the cell from that sheet that you want to pull in.

HTH Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[mistos]

I have entered the following formula, in which PBB Install is the name of the first worksheet and F31 which is the cell I am looking to referance. Excel accepts the formula but a value of 0 is displayed in the cell.

=INDIRECT('PBB Install'! & "!F31&quot

 

[mistos]

I beleive I have found the error in the above formula. It appears PBB Install is the name of the cell to the left of the cell the formula is in. That name was typed in by me and is named the same as the worksheet I am looking to referance. I am assuming that if there a way I can have the name of the worksheets automatically enter into the tally sheet, that the problem may resolve itself. Does anyone know how I can have the worksheet names automatically enter themselves onto a tally sheet?????

 

[SkipVought]

Hi,
I am not sure if I understand what you are REALLY wanting. Sometimes we look at a solution from one perspective. Maybe this perspective could be helpful.

Instead of linking all your sheets to a Tally Sheet with formulae, which can tend to get confusing as the workbook matures, Consider using the Tally sheet as follows:

1. Clear all data from the Tally Sheet

2. Copy data from each sheet to the Tally Sheet

3. Sort and SubTotal as required

If you need to retain Sheetname, that can be retained in the copy process. This kind of process can be easily automated via VBA.

More than one way to skin a cat Skip,
metzgsk@voughtaircraft.com

 

[AngO]

HMMM
Have you considered the Data/consolidate function?
It sounds exactly like the thing you are looking for.

Start in the desination (Tally Sheet), Data/Consolidate.
In the reference area, select the range from each file. If the data is in exactly the same space, you don't need to include the headings on top & left. If it varies at all (mine always do), include the headings in your selection, and make sure you choose labels top and left. Then click on the ADD button. Continue until each file has been added to the reference section.

Now, if you want it to update automatically, anytime the other sheet change, click the button to create links to source data. Note: this will cause additional rows to added to the destination location, that are setup as an outline and hidden from view. Just a normal thing that worries people.

Once you have everything set in the window, Tell it okay! It will do the rest automatically!!

Best Wishes,
AngO