Incrementing a formula within an excel cell

[James C.]

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

 

[T. Valko]

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

 

[James C.]

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]

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)

 

[James C.]

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]

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

 

[James C.]

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?

 

[James C.]

Nevermind... just figured it out.

Thanks again for the help

 

[T. Valko]

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

 

[T. Valko]

Good deal. Thanks for the feedback!