If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
changing multiple column references in a formula
A sheet contains a list of prices that change weekly - 30 products vertically
each with a column for each weeks price. Each week a new column is added to the end of the sheet with that week's price. Another sheet has a summary of the products and the starting price and finishing price on a monthly basis. As the columns with the two prices change inconsistently each month I need an easy way for an Excel novice to change the column references in the summary formulas for the starting and finishing prices. Question: Can a formula contain a column variable which is lookedup from another cell. E.g. two new reference cells for the starting and finishing price which contain the two column reference values that all relevant formulas would automatically update to. I appreciate any help. |
#2
|
|||
|
|||
changing multiple column references in a formula
Suppose this is the layout in Sheet1:
A B C D E pname w1 w2 w3 w4 ... p1 p2 .... and this in Sheet2: A B C 1 product B E 2 p1 * Enter column of start week in B1 (in my example B) Enter column of finish week in C1 (in my example E) Replace * in B2 by formula =INDIRECT("Sheet1!"&B$1&ROW()) and fill it to the right and down! Regards, Stefi „JackRosieMaisie” ezt *rta: A sheet contains a list of prices that change weekly - 30 products vertically each with a column for each weeks price. Each week a new column is added to the end of the sheet with that week's price. Another sheet has a summary of the products and the starting price and finishing price on a monthly basis. As the columns with the two prices change inconsistently each month I need an easy way for an Excel novice to change the column references in the summary formulas for the starting and finishing prices. Question: Can a formula contain a column variable which is lookedup from another cell. E.g. two new reference cells for the starting and finishing price which contain the two column reference values that all relevant formulas would automatically update to. I appreciate any help. |
#3
|
|||
|
|||
changing multiple column references in a formula
Hi,
You dont need to change the formula you can lookup that last value in a row like this:- =LOOKUP(2,1/(Sheet1!A2:Z2""),Sheet1!A2:Z2) Finding the second to last involves an array formula, see below INDEX(Sheet1!A2:Z2,LARGE(IF(Sheet1!A2:Z2"",COLUM N(Sheet1!A2:Z2)-COLUMN(A2)+1),2)) Note both these formula work on the range A2 to Z2 so change to suit. In the second formula the last 2 denotes find the 2nd to last value so change to a 3 for the third to last etc but note the array formula instructions below. This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "JackRosieMaisie" wrote: A sheet contains a list of prices that change weekly - 30 products vertically each with a column for each weeks price. Each week a new column is added to the end of the sheet with that week's price. Another sheet has a summary of the products and the starting price and finishing price on a monthly basis. As the columns with the two prices change inconsistently each month I need an easy way for an Excel novice to change the column references in the summary formulas for the starting and finishing prices. Question: Can a formula contain a column variable which is lookedup from another cell. E.g. two new reference cells for the starting and finishing price which contain the two column reference values that all relevant formulas would automatically update to. I appreciate any help. |
#4
|
|||
|
|||
changing multiple column references in a formula
On 17 June, 09:22, JackRosieMaisie
wrote: A sheet contains a list of prices that change weekly - 30 products vertically each with a column for each weeks price. Each week a new column is added to the end of the sheet with that week's Hi, My first reply tthrough the web interface seems to have got lost so I'll try again. To find the last value in a row try =LOOKUP(2,1/(Sheet1!A2:Z2""),Sheet1!A2:Z2) and to find the second to last try this array formula =INDEX(Sheet1!A2:Z2,LARGE(IF(Sheet1!A2:Z2"",COLU MN(Sheet1!A2:Z2)- COLUMN(A2)+1),2)) Note the last 2 denotes second to last so change to 3 for 3rd to last. This is an array formula which must be entered by pressing CTRL+Shift +Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike Another sheet has a summary of the products and the starting price and finishing price on a monthly basis. As the columns with the two prices change inconsistently each month I need an easy way for an Excel novice to change the column references in the summary formulas for the starting and finishing prices. Question: Can a formula contain a column variable which is lookedup from another cell. E.g. two new reference cells for the starting and finishing price which contain the two column reference values that all relevant formulas would automatically update to. I appreciate any help. |
#5
|
|||
|
|||
changing multiple column references in a formula
Thanks Mike and if the columns required were consistently the last and n from
last that would be a great solution. Unfortunately, it varies (some months have four weeks, some have five; plus other factors mean that it is inconsistent) which is why I'm keen to have a reference to a user-defined cell with the two column references. Especially as there are several tables with many rows ALL using the same start and finish column references. But thanks anyway. "Mike H" wrote: Hi, You dont need to change the formula you can lookup that last value in a row like this:- =LOOKUP(2,1/(Sheet1!A2:Z2""),Sheet1!A2:Z2) Finding the second to last involves an array formula, see below INDEX(Sheet1!A2:Z2,LARGE(IF(Sheet1!A2:Z2"",COLUM N(Sheet1!A2:Z2)-COLUMN(A2)+1),2)) Note both these formula work on the range A2 to Z2 so change to suit. In the second formula the last 2 denotes find the 2nd to last value so change to a 3 for the third to last etc but note the array formula instructions below. This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "JackRosieMaisie" wrote: A sheet contains a list of prices that change weekly - 30 products vertically each with a column for each weeks price. Each week a new column is added to the end of the sheet with that week's price. Another sheet has a summary of the products and the starting price and finishing price on a monthly basis. As the columns with the two prices change inconsistently each month I need an easy way for an Excel novice to change the column references in the summary formulas for the starting and finishing prices. Question: Can a formula contain a column variable which is lookedup from another cell. E.g. two new reference cells for the starting and finishing price which contain the two column reference values that all relevant formulas would automatically update to. I appreciate any help. |
#6
|
|||
|
|||
changing multiple column references in a formula
Thanks Stefi. That's just what I wanted.
"Stefi" wrote: Suppose this is the layout in Sheet1: A B C D E pname w1 w2 w3 w4 ... p1 p2 ... and this in Sheet2: A B C 1 product B E 2 p1 * Enter column of start week in B1 (in my example B) Enter column of finish week in C1 (in my example E) Replace * in B2 by formula =INDIRECT("Sheet1!"&B$1&ROW()) and fill it to the right and down! Regards, Stefi „JackRosieMaisie” ezt *rta: A sheet contains a list of prices that change weekly - 30 products vertically each with a column for each weeks price. Each week a new column is added to the end of the sheet with that week's price. Another sheet has a summary of the products and the starting price and finishing price on a monthly basis. As the columns with the two prices change inconsistently each month I need an easy way for an Excel novice to change the column references in the summary formulas for the starting and finishing prices. Question: Can a formula contain a column variable which is lookedup from another cell. E.g. two new reference cells for the starting and finishing price which contain the two column reference values that all relevant formulas would automatically update to. I appreciate any help. |
Thread Tools | |
Display Modes | |
|
|