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

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