autofill?

[robz]

ok i have a File.. preety big that has tabs like these on the bottom ..
Z1, Z2, Z3, Z4.. all the way too Z979

Now i have a summary sheet in the Fron where i have this in the cell:
=Z1!A1
=Z2!A1
=Z3!A1

...


now is there a way i can auto fill this all way down.. someone pleas
help.. i need to get this done than

 

[David McRitchie]

I don't think you want sheet name that could be confused by
Excel as cell addresses or defined range names.

You can rename your sheets to anyname that you want to
use and you build a table of contents at front, see
http://www.mvps.org/dmcritchie/excel/buiildtoc.htm
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
set up to create hyperlinks to the other sheets.

 

[Max]

Put in any starting cell:
=INDIRECT("'Z"&ROW(A1)&"'!A1")
Copy down a total of 979 rows

 

[Gord Dibben]

robz

979 worksheets?

=INDIRECT("Z"&ROW()&"!A1")

Gord Dibben Excel MVP

 

[Dave Peterson]

One more way would be to build the formulas as strings and then convert those
strings back to formulas:

Put this formula in Row 1:

="='z" &ROW() &"'!a1"

The drag down.

Then select your range and copy|paste special values
and finally
Edit|replace
what: = (equal sign)
with: = (equal sign)

And you'll have formulas.

One of the differences between this kind of thing and the =indirect() worksheet
function is that this will only reevaluate when the cell in the formula changes.

=indirect() is a volatile function--that means that it recalcs each time excel
recalcs.

=====
One tip about that edit|replace bit.

Do it on a couple cells first--just to check your typing. If you made a
mistake, then excel will prompt you for the workbook it should use. And if you
change about 1000 entries, you'll be pretty busy dismissing dialogs.

 

[Gord Dibben]

Good point David.

Gord