| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula that uses a cell ref that contains a range name?
Using Excel 2007.
WS1 has a list where the header row contains range names. example; B1 contains CUSTOMER, C1 contains ACCOUNT. WS2 has a table where J2:J10 is a range named CUSTOMER L2:L10 is a range named ACCOUNT Normally I might use '=CUSTOMER' and '=ACCOUNT' for each named range but there are many. So, I'm trying to construct a single formua on WS1 that I can copy to all the cells in the list that uses the contents of the header cell to return the value in the corresponding cell of the named range. For example, the the result in WS1-B5 would be the value of WS2-J5. Thanks for any ideas. Dave |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need a formula that uses a cell ref that contains a range name?
In B1 (WS1) enter
=OFFSET(INDIRECT(A$1),ROW()-2,0) and copy across and down... "Bassman62" wrote: Using Excel 2007. WS1 has a list where the header row contains range names. example; B1 contains CUSTOMER, C1 contains ACCOUNT. WS2 has a table where J2:J10 is a range named CUSTOMER L2:L10 is a range named ACCOUNT Normally I might use '=CUSTOMER' and '=ACCOUNT' for each named range but there are many. So, I'm trying to construct a single formua on WS1 that I can copy to all the cells in the list that uses the contents of the header cell to return the value in the corresponding cell of the named range. For example, the the result in WS1-B5 would be the value of WS2-J5. Thanks for any ideas. Dave |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need a formula that uses a cell ref that contains a range name
I couldn't get your formula to work in my case but I found another method by
matching column headers instead of using range names. HLOOKUP(INDIRECT("R1C",FALSE),ImpTable,ROW(),FALSE ) Thanks for your input. Dave "Sheeloo" wrote: In B1 (WS1) enter =OFFSET(INDIRECT(A$1),ROW()-2,0) and copy across and down... "Bassman62" wrote: Using Excel 2007. WS1 has a list where the header row contains range names. example; B1 contains CUSTOMER, C1 contains ACCOUNT. WS2 has a table where J2:J10 is a range named CUSTOMER L2:L10 is a range named ACCOUNT Normally I might use '=CUSTOMER' and '=ACCOUNT' for each named range but there are many. So, I'm trying to construct a single formua on WS1 that I can copy to all the cells in the list that uses the contents of the header cell to return the value in the corresponding cell of the named range. For example, the the result in WS1-B5 would be the value of WS2-J5. Thanks for any ideas. Dave |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need a formula that uses a cell ref that contains a range name?
Hi,
Try this =INDEX(INDIRECT(B$1),ROW(A1),1) -- Thanks, Shane Devenshire "Bassman62" wrote: Using Excel 2007. WS1 has a list where the header row contains range names. example; B1 contains CUSTOMER, C1 contains ACCOUNT. WS2 has a table where J2:J10 is a range named CUSTOMER L2:L10 is a range named ACCOUNT Normally I might use '=CUSTOMER' and '=ACCOUNT' for each named range but there are many. So, I'm trying to construct a single formua on WS1 that I can copy to all the cells in the list that uses the contents of the header cell to return the value in the corresponding cell of the named range. For example, the the result in WS1-B5 would be the value of WS2-J5. Thanks for any ideas. Dave |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need a formula that uses a cell ref that contains a range name?
And a slightly shorter version
=INDEX(INDIRECT(B$1),ROW(A1)) -- Thanks, Shane Devenshire "Bassman62" wrote: Using Excel 2007. WS1 has a list where the header row contains range names. example; B1 contains CUSTOMER, C1 contains ACCOUNT. WS2 has a table where J2:J10 is a range named CUSTOMER L2:L10 is a range named ACCOUNT Normally I might use '=CUSTOMER' and '=ACCOUNT' for each named range but there are many. So, I'm trying to construct a single formua on WS1 that I can copy to all the cells in the list that uses the contents of the header cell to return the value in the corresponding cell of the named range. For example, the the result in WS1-B5 would be the value of WS2-J5. Thanks for any ideas. Dave |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Add same formula to every cell in the range | Excel Discussion (Misc queries) | |||
| Sum Formula for cell range | Excel Discussion (Misc queries) | |||
| How can I make a blank cell in a formula cell with a range of cell | Excel Discussion (Misc queries) | |||
| VBA for Last Cell in Formula Range | Excel Discussion (Misc queries) | |||
| formula for named cell/range using cell values | Excel Worksheet Functions | |||
Linear Mode
