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

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

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

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

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