Row & column portions of a cell as variables

[Tigerxxx]

Hello,

Is it possible for me to make the column as well as the row portion of a
reference cell variable?
Example- If the reference is to cell B4, can I make "B" as a variable using
a formula as well as make "4" as a variable using a formula?

Please advise.
Thank you.

 

[Jim Thomlinson]

Take a look at the Indirect function.

 

[T. Valko]

Try something like this:

A1 = B
A2 = 4
B4 = Yes

=INDIRECT(A1&A2)

result = Yes

 

[Tigerxxx]

Thank you.
However please let me explain what I am trying to do:

From Cell B1 onwards to the right, I have months Dec-08 to Jan-05 from left
to right
Row 2 has the data corresponding to the above months.
Each month I input data as it is available i.e. currently the latest data is
in cell D2 for Oct 08 which means cells B2 & C2 related to the data for
Dec-08 & Nov-08 are empty.
In Cell A2, I am caluculating a maximum value for the last 12 months using
the MAX formula i.e. currently I am calculating the maximum value of cells
D2:O2 (Oct 08-Nov 07) using MAX (D2:O2)
How can I write the MAX formula differently so that when I put in the data
for Nov 08 in cell C2, the formula in cell A2 will automatically change as
MAX (C2:N2)?

Appreciate your response.

 

[T. Valko]

Try this array formula**.

Assumes there will *always* be at least 12 numeric values in the range and
that there is nothing in the range but numeric values.

=MAX(B2:INDEX(B2:T2,SMALL(IF(B2:T2,COLUMN(B2:T2)-MIN(COLUMN(B2:T2))+1),12)))

Change T2 to the actual last cell in your range.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Tigerxxx]

Thank you very much...I will try it out!

Try this array formula**.

Assumes there will *always* be at least 12 numeric values in the range and
that there is nothing in the range but numeric values.

=MAX(B2:INDEX(B2:T2,SMALL(IF(B2:T2,COLUMN(B2:T2)-MIN(COLUMN(B2:T2))+1),12)))

Change T2 to the actual last cell in your range.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)