Building a reference by concatenating components

E

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!
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top