How do I Edit Fill Down a Cell Reference to Multiple Worksheets

[langba]

I have one main worksheet that summarizes the calculations on many other
worksheets.

I want to have a column that will Edit, Fill, Down like the following
example (z1 always stays the same no matter what the worksheet):

=a!z1
=b!z1
=c!z1

But all I get when I do an Edit, Fill, Down is:

=a!z1
=a!z2
=a!z3

I went through the reference help and 3D help, but nothing seemed to
work. If I must have a hidden column with the worksheet names, and
somehow reference the name in the formula, that is OK. I couldn't
figure that one out either.

 

[Biff]

Hi!

If your sheet names really are A, B, C etc.

=INDIRECT(CHAR(ROW(A97))&"!Z1")

Copy down as needed.

You can use this for up to 26 sheets as the alphabet runs out after:

=INDIRECT(CHAR(ROW(A122))&"!Z1")

If your sheet names are something else then you would need to list those
names in a range, say, A1:An, then:

=INDIRECT(A1&"!Z1")

Copy down as needed.

Biff