I need it the other way around
What I need is =
indirect(Z1&"'Tab2'!"&350"

Not following you on this.

If Z1 holds a letter that represents the column letter the resulting
reference has to be of the form:

sheet name! column letter row number

indirect(Z1&"'Tab2'!"&350"

That does:

column letter sheet name! row number

--
Biff
Microsoft Excel MVP


"James C." wrote in message
...
One last question (I promise). I need it the other way around

What if the 350 is on the other Tab? What I need is =
indirect(Z1&"'Tab2'!"&350"

I tried this and it gives me a Ref error?

"T. Valko" wrote:

You can either hardcode it like this:

=INDIRECT("'Tab1'!"&Z1&"350")

Or, you can enter the sheet name in a cell then refer to that cell:

A1 = Tab1

=INDIRECT("'"&A1&"'!"&Z1&"350")

It can get really cryptic and confusing with all those quotes! (and they
have to be in the all the right places!)

--
Biff
Microsoft Excel MVP


"James C." wrote in message
...
That is perfect and easy... One last question. This works perfect if
all
data
is on the same page but how would it work when my input and formula is
on
Tab2 but data is on Tab1. I tried the following and it gave me an error

='Tab1'!Indirect(Z1&350)
=Indirect(Z1&'Tab1'!350)

Both did not work. Any ideas?

"T. Valko" wrote:

In other words you want to "build" the cell reference x350 by
selecting a
month from a drop down?

If cell Z1 = A, B, C, D etc...

=INDIRECT(Z1&350)

--
Biff
Microsoft Excel MVP


"James C." wrote in message
...
Maybe this will help simplify what I am trying to do.

I have a formula in a cell that says =A350 (where "A" is January)

In Cell A1 I have a dropdown where the user can select the month and
the
reference cell Z1 will have the corresponding value (January = A,
February
=
B... and so on).

Instead of the formula saying = A350, I want it to say ="Z1"350,
where
Z1
is
populated by my drop down

Is this possible? Basically controlling the column part of the
formula
with
another cell.



"T. Valko" wrote:

Kind of hard to "visualize" what you're trying to do but this might
set
you
in the right direction.

If you have 12 columns of data, one for each month of the year, you
can
refer to the monthly column by using the month number.

Sheet2 A1:L1 = monthly data for Jan to Dec.

=INDEX(Sheet2A1:L1,1) refers to the Jan column
=INDEX(Sheet2A1:L1,12) refers to the Dec column

Sheet1 A1 = 1

=INDEX(Sheet2A1:L1,A1) refers to the Jan column


--
Biff
Microsoft Excel MVP


"James C." wrote in message
...
Hi all,

I am trying to accomplish the following and can't seem to figure
it
out. I
have a tab of monthly data Jan - Dec where Jan is in Cell A1...

I have a second tab that links to tab 1. Tab 2 is a one month at
a
time
snapshot. So right now all cells are linked to January on Tab 1.
Instead
of
doing a Find/Replace on formulas on Tab 2 to switch the month to
say
Feb
is
there a way I can put in box in cell A1 of tab 2 where the user
can
input
a
month (A - L). Then instead of linking directly to A1 on the
other
tab
I
can
insert a formula that says Start with A1 if there is something in
the
box
the
substitute the "A" with whatever is in the box? Ie for March it
would
be
"C"
thus C1?

Thanks