Incrementing a formula within an excel cell

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

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

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

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

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

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

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

Nevermind... just figured it out.

Thanks again for the help

"James C." wrote:

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

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

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"James C." wrote in message
...
Nevermind... just figured it out.

Thanks again for the help

"James C." wrote:

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