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. |
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. |
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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 11:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com