Insert Tab Name Into Formula

E

edavies

I have a fairly complex Excel spreadsheet containing 40 worksheets with
about 7K cells per worksheet. One set of worksheets within the
spreadsheet summarizes data from others. It would make formatting
these summary sheets easier if I could automate inserting the worksheet
tab names into the reference formula. On each summary sheet I would
have a column of the tab names then another column with the reference
formulas. I want each formula to "read" the corresponding tab name.

Here's an example: one worksheet tab is "GPWEB" and another is
"INDSL". Rather than have to create two separate formulas to get the
summary data (e.g., =GPWEB!G$71 & =INDSL!G$71), I want a single formula
that essentally would work as =(GET TAB NAME)G$71.

Is this even possible to do? Do I have to create a macro? Which is
something I have never done.

Thanks.
 
J

JE McGimpsey

Here's an example: one worksheet tab is "GPWEB" and another is
"INDSL". Rather than have to create two separate formulas to get the
summary data (e.g., =GPWEB!G$71 & =INDSL!G$71), I want a single formula
that essentally would work as =(GET TAB NAME)G$71.

Is this even possible to do? Do I have to create a macro? Which is
something I have never done.
Click to expand...

One way:

=INDIRECT("'" & A1 & "'!G71")

where A1 contains the tab name. To see how to get the tab names in a
cell (even if the user changes them) see

http://www.mcgimpsey.com/excel/formulae/cell_function.html
 
Top