Building a reference by concatenating components

[Ellis]

I'm creating a spreadsheet that will compare monthly performance of my
company with the budgeted amounts. Each month, I will import a file from
QuickBooks, creating an individual page for each month as they go along. I
will name each worksheet "January," "February," etc.

I am heading each column using the name of the month as it appears on each
of the component sheets.

I want to create a reference to each of the monthly sheets on the master
sheet such that each column will abstract the appropriate info from the
imported sheet.
I should be able to add a formula that will allow me to copy the columns
across, simply substituting the name of the Month in the formula.

Assuming that the header of the column containing "January" is in cell G2 of
the master sheet, I should be able to make a reference to a cell on the
"January" worksheet as follows: ="="&G2&"!G12". When I enter that in cell G14
of the master sheet, it shows "=January!G12" in the cell instead of the
contents of that cell on the January worksheet, January!G12.

I'm sure it's just improper use of the quotes and equal signs because I've
done this many times in the past; but I just don't remember what I did.

Help!

 

[T. Valko]

Try this:

=INDIRECT(G2&"!G12")

Biff

 

[Ellis]

Biff:

Thanks for the quick—and perfect—response!

Gratefully,

Ellis

 

[Dave Peterson]

If you ever have a worksheet that looks like: January 2007, you'll want a
formula like:

=INDIRECT("'" & G2 & "'!G12")

If the apostrophes aren't needed, then they won't do any harm.

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Biff:

Thanks for the quick-and perfect-response!

Gratefully,

Ellis