Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row & column portions of a cell as variables
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row & column portions of a cell as variables
Take a look at the Indirect function.
-- HTH... Jim Thomlinson "Tigerxxx" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row & column portions of a cell as variables
Try something like this:
A1 = B A2 = 4 B4 = Yes =INDIRECT(A1&A2) result = Yes -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row & column portions of a cell as variables
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" wrote: Try something like this: A1 = B A2 = 4 B4 = Yes =INDIRECT(A1&A2) result = Yes -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row & column portions of a cell as variables
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) -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... 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" wrote: Try something like this: A1 = B A2 = 4 B4 = Yes =INDIRECT(A1&A2) result = Yes -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row & column portions of a cell as variables
Thank you very much...I will try it out!
"T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... 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" wrote: Try something like this: A1 = B A2 = 4 B4 = Yes =INDIRECT(A1&A2) result = Yes -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change font in portions of cell with many characters (1000's) | Excel Discussion (Misc queries) | |||
Protecting portions of worksheet | Excel Worksheet Functions | |||
From single cell variables to a single column serie | New Users to Excel | |||
Locking portions of a formula | Excel Worksheet Functions | |||
Copying portions of data | Excel Discussion (Misc queries) |