Substituting cell content into a formula

[Guest]

I have a workbook that has worksheets for each month, containing a table of
rows of customer order info for the month. All tables are formated the same.
I have a report page that nicely performs calculations and formats a report
for a sinlge row of data from a single worksheet for a one customer. How do I
set up my formulas so that I can substitute a different month (worksheet
name) and row number in the formulas to get the save report for a different
customer and/or month?

More simply put, I have a hard coded formula =Jan!A4 in my report that I'd
like to change the worksheet and row specification to say =Feb!A22 based on
the contents of a cell containing the text "Feb" and another cell containing
"22"?

 

[Rob Bovey]

More simply put, I have a hard coded formula =Jan!A4 in my report that I'd
like to change the worksheet and row specification to say =Feb!A22 based
on
the contents of a cell containing the text "Feb" and another cell
containing
"22"?

Hi John,

If "Feb" is in cell A1 and "22" is in cell A2, the following formula
will do what you're looking for:

=INDIRECT(A1 & "!A" & A2)

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Dave Peterson]

=indirect("'" & a1 & "'!" & b1)

if a1 and b1 are the two cells.

 

[Dave Peterson]

Oops. I had "A22" in B1.

Don't use my formula!

=indirect("'" & a1 & "'!" & b1)

if a1 and b1 are the two cells.

 

[Guest]

Rob, this is great. Solves everything except if I later move column "A" to
another location in the referenced worksheet. How do I get the actual column
LETTER dynamically so I can make this reference follow future changes to the
referenced WS. I tried modifying your suggetion something like:
=INDIRECT($AA$2 & "!" & COLUMN(INDIRECT($AA$2 & !A1)) & $AA$4)
where $AA$2 is reprot cell containing the ref WS name & $AA$4 the row ref.
This all seems a little convoluted and complicated to me, and I may have the
syntax screwed up, but it won't work anyway, because COLUMN return a col
number, not the col letter.

How do I get the Col LETTER?
Is there a simpler formula to do this?

 

[Rob Bovey]

Hi John,

Since you're already hard-coding the Sheet name and row number in other
cells I'd just add the column letter to the list of inputs as well.

A1 = "Feb"
A2 = "22"
A3 = "A"

=INDIRECT(A1 & "!" & A3 & A2)

Now you can change any of the three source cells to move the reference
as appropriate. To answer your other question, you can get the column letter
for any column with the following formula:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm