EXCEL: creating summary table referencing multiple worksheet cells 1

[Hueby]

I'm looking for the easiest/quickest method to accomplish the below:


Column A B C
row 1 ='1'!$T$58 ='1'!$B$60 ='1'!$C$30
row 2 ='2'!$T$58 ='2'!$B$60 ='2'!$C$30
row 3 ='3'!$T$58 ='3'!$B$60 ='3'!$C$30
...
row 50

I have one workbook with up to 50 worksheets (named 1 thru 50). I am creating a 'summary' worksheet that will reference various cells from all the worksheets and provide a summary in a table like shown above. How do I quickly accomplish this without manually creating each line in each column and going in and entering each worksheet 1, 2, 3, all the way to 50 ???

Let me know if this isn't clear... THANK YOU!

 

[SkipVought]

hi,

What a mess!

What you have is an Excel maintenance NIGHTMARE!

The short direct answer is
[tt]
=INDIRECT(ROW()&"!$T$58")
[/tt]
for example. Copy that down thru your 50 rows.

Use the INDIRECT() function in columns B & C as well.

But let me suggest that there has to be a much MUCH MUCH better way to 1) structure your workbook and 2) summarize you data. You've used MS Access, I see, so you know something about relational databases and table structure. Excel is definitely NOT a serious database, although you can do database-like things in Excel and tabular data ought to be structured similarly. Tables can be summarized without explicit reference to a cell on another sheet.

Your workbook, seems to me, to be a disaster waiting to happen, IMNSHO.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Hueby]

I hear ya... Thanks for the input on my workbook being a nightmare! lol... It's just a one time proposal, so as soon as I get this summary put together it's over with!

I can't seem to get this to work... So I'm playing with INDIRECT statement now...

I am in the 'summary worksheet' and have the following:

column AV
1 (these are the names of my worksheets in the workbook)
2
3

column AW
=INDIRECT("'"&AV1&"'!T58")
=INDIRECT("'"&AV2&"'!T58")
=INDIRECT("'"&AV3&"'!T58")

What this INDIRECT statement should do is....

I have in column AV the numbers 1, 2, 3. These are the names of the worksheet 1, 2, 3 that I need the value referenced in cell T58 (from each of the worksheets) on the summary.

So the INDIRECT code in column AW is setup to do that? Where is my error? And once I get this code to work, then I'll be able to fill drag down to 50 rows or whatever.

 

[Gruuuu]

Make sure the tab names are exactly equivalent to your cell values. i.e. no extra spaces or whatever.

Are your tabs REALLY named "1","2","3", etc?