need a formula that uses a cell ref that contains a range name?

[Bassman62]

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

 

[Sheeloo]

In B1 (WS1) enter
=OFFSET(INDIRECT(A$1),ROW()-2,0)
and copy across and down...

 

[Bassman62]

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

 

[ShaneDevenshire]

Hi,

Try this

=INDEX(INDIRECT(B$1),ROW(A1),1)

 

[ShaneDevenshire]

And a slightly shorter version

=INDEX(INDIRECT(B$1),ROW(A1))