Shortcut for referring to tab names in formulas

[Guest]

Hello..thanks for your help in this..

I have a workbook with many tabs (worksheets). The last is a spreadsheet
(let's call it Tab#10) with each row drawing data from different places in
each of the worksheets...so that row 1 will draw data from tab#1, row two
from tab #2...etc.

I would like to manually link the cells in row one (in Tab#10) to tab#1, and
then drag down to fill the rest of the rows (in Tab#10). I was then going to
use the "search & replace" feature to replace the reference to tab#1 by the
correct tab name for row 2 onwards.

QUESTION: There must be a smarter/quicker/more efficient way of doing this,
and I know one of you Excel whizzes knows it!!

p.s. I am not a macro expert..but I just copied a macro code from this site
for automatically naming tabs and it WORKED - I am SO happy.. so I'm
encouraged.

 

[John Michl]

Try using the INDIRECT function. In tab 10, list the tab names
starting in cell A1 through A9. In cell B1 enter the formula:
=INDIRECT(A1&"!a1"). Copy this down and you'll pull in the value of
cell A1 from each of the named tabs. Change the references to suit
your sheet layout. Not that the first A1 in the formula refers to the
cell on Tab10 that list the first tab name. The second A1 should be
changed to whatever cell contains the data your are retrieving.

- John
www.JohnMichl.com

 

[Gord Dibben]

Stilla

If Sheet1, Sheet2 etc. enter this formula in A1 of your master sheet.

=INDIRECT("'Sheet" & (ROW() & "'!$A$1") assuming A1 on each sheet is your
referenced cell.

Drag/copy down column A.

If sheets have unique names, enter the sheet names in a column and use this
formula.

=INDIRECT(B1 & "!$A$1")

If spaces in sheetnames use =INDIRECT("'" & B1 & "'!$A$1")

Assume sheet names were in B1:B10 you would copy down 10 cells.

Your ranges may differ, so adjust to suit.


Gord Dibben Excel MVP

 

[Guest]

OH MY STARS!! OH GEEEEE WHIIZZZZZ! I'm JUMPING UP AND DOWN!!! I'M
HYPERVENTILATING!!! THIS IS @@@@@W O N D E R F U L@@@@

You just saved me at least half a day's work!!! I'm going to have about a
hundred of these files to do! Now I can just create one prototype !

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU

 

[Guest]

Thank you too Gord! You guys are simply terrific!

 

[John Michl]

Gee, I feel all warm and fuzzy now. Glad I could help.

- John