View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
John John is offline
external usenet poster
 
Posts: 2,069
Default Substituting cell content into a formula

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" wrote:

"John" wrote in message
...
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