Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to choose from the last column of a given row
I have a table that looks like this:
Item 7/9 7/10 7/11 A 23 342 2323 B 343 219 643 Every day I add a new column with some additional data. Sometimes I add a new row for a new item. I would like a second table that will draw from the first table, and give me the values in the last two columns of the first table for each item. So it might look like this: Item Col1 Col 2 A 342 2323 B 219 643 Is there a formula I can use that will always pull the last two columns' worth of data for me? Thanks much, David |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to choose from the last column of a given row
On Thu, 12 Jul 2012 16:10:46 -0700 (PDT), David Tannenbaum wrote:
I have a table that looks like this: Item 7/9 7/10 7/11 A 23 342 2323 B 343 219 643 Every day I add a new column with some additional data. Sometimes I add a new row for a new item. I would like a second table that will draw from the first table, and give me the values in the last two columns of the first table for each item. So it might look like this: Item Col1 Col 2 A 342 2323 B 219 643 Is there a formula I can use that will always pull the last two columns' worth of data for me? Thanks much, David IF your data table begins in A1; and if there are no blank cells in column A or row 1, then one way is to use a dynamic range name to define your data table: eg: Define Name Name: DataTbl Scope: Workbook Refers To: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) Then, with your 2nd table starting in column M (adjust the references depending on where you have this new table) To return col1 for Item A (where "A" is in M15): =INDEX(DataTbl,MATCH($M15,$A:$A,0),COLUMNS(DataTbl )-1) To return col2 for Item A: =INDEX(DataTbl,MATCH($M15,$A:$A,0),COLUMNS(DataTbl )) And with your Item names in col M; just select N15:O15 and fill down as far as required. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to choose from the last column of a given row
Awesome! Thank you!
On Thursday, July 12, 2012 5:01:08 PM UTC-7, Ron Rosenfeld wrote: On Thu, 12 Jul 2012 16:10:46 -0700 (PDT), David Tannenbaum wrote: I have a table that looks like this: Item 7/9 7/10 7/11 A 23 342 2323 B 343 219 643 Every day I add a new column with some additional data. Sometimes I add a new row for a new item. I would like a second table that will draw from the first table, and give me the values in the last two columns of the first table for each item. So it might look like this: Item Col1 Col 2 A 342 2323 B 219 643 Is there a formula I can use that will always pull the last two columns' worth of data for me? Thanks much, David IF your data table begins in A1; and if there are no blank cells in column A or row 1, then one way is to use a dynamic range name to define your data table: eg: Define Name Name: DataTbl Scope: Workbook Refers To: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) Then, with your 2nd table starting in column M (adjust the references depending on where you have this new table) To return col1 for Item A (where "A" is in M15): =INDEX(DataTbl,MATCH($M15,$A:$A,0),COLUMNS(DataTbl )-1) To return col2 for Item A: =INDEX(DataTbl,MATCH($M15,$A:$A,0),COLUMNS(DataTbl )) And with your Item names in col M; just select N15:O15 and fill down as far as required. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CHOOSE from a column | Excel Discussion (Misc queries) | |||
How to indicate in formula to choose the entire column starting from a specified cell? | Excel Discussion (Misc queries) | |||
how i choose the column from a combo box | Excel Discussion (Misc queries) | |||
AUTOMATICALLY CHOOSE COLUMN H OR G DEPENDANT ON CONTENT IN COLUMN. | Excel Programming | |||
choose more than 1 column | Excel Programming |