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