How do I reference the same cell, but in different worksheets?

[marcon]

Hi,

I am trying to reference a specific cell address (say B5) from each
sheet in my workbook, and produce a column that contains B5's value
from each sheet. Hopefully this is enough info for someone to correctly
analyse the issue i am having. Thanks.

Al

 

[Guest]

Create a helper column, say D1, D2, etc. that you will put all of your sheet
names in. In E1, then, use this formula and copy down:

=INDIRECT(D1&"!B5")

Does that help?

 

[Glenn Rathke \(Soft Design Consulting\)]

Why not cycle thru the Worksheet collection? Then if sheets are added or
deleted, the code always works.

Glenn

 

[marcon]

i guess i am also trying to link the names of each of the worksheets to
a column in my master summary sheet. Basically, i am trying to make a
template that updates the names of each of the sheets based on whatever
the value is in the master sheets column.

 

[Guest]

This is exactly what I'm trying to do, but I'm missing something somewhere.
My tab names are Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc. Assume I'm
trying to reference cell D10 from each worksheet. What should my formula be?
It worked for me as long as my tab names were Sheet1, Sheet2, Sheet3, etc.;
but when I created a column and entered Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2
pm, etc., I got the #REF error. Help, please. Thank you...

 

[PCLIVE]

What is your formula which works with the original sheet names?

 

[Guest]

Thanks for the response. I'm just getting a cell reference that contains a
sum of one of the columns in each worksheet. I want each of those totals in a
worksheet where I can total all of them. I can manually reference each cell,
but I was hoping there was a faster way to do it.

 

[Dave Peterson]

I'm not sure I understand your question, but some worksheet names need to be
surrounded by apostrophes when used as a reference:

='sheet 2'!a1

So maybe:
=INDIRECT("'" & D1 & "'!B5")

is what you're looking for.

 

[Guest]

Thank you for the help. I have a worksheet named Total. In cell C1, I want
the total from sheet Nov 1 am, cell D10. In cell C2, I want the total from
sheet Nov 1 pm, cell D10. In cell 3, I want the total from sheet Nov 2 am,
cell D10, etc.

 

[RagDyer]

Try this in C1:
=INDIRECT("'Nov "&ROWS($1:2)/2&" am'!D10")

And try this in C2:
=INDIRECT("'Nov "&ROWS($1:2)/2&" pm'!D10")

Then, select *both* C1 and C2, and click on the fill handle of that 2 cell
selection, and drag down as needed.

 

[Guest]

Thank you. That's way beyond anything I would ever figure out. What does it
mean? Can you translate it for me? I am getting an "Inconsistent Formula"
warning on every other row, but it seems to be working fine. Thanks again.
This is really helpful.