Can a cell result be a range name?

Hiya,

I'm trying to get a formula to show me the value of one cell based on the value of another to automate some internal processes. Cell J3 returns today's date, then J4 says what week number that is. J5 concatenates the two to make the look up. Column A is named range "PN", and columns C onwards are named ranges to match the column heading.

What I'm wanting is for the result in test cell 2 (J8), is to return the stock requirements for the current week, based on the result of cell J5.

I can do this by using nested IF statements, but that would be 8 cells to calculate, then another one to find the cell that has a value. If there a way to get Excel to recognize the value in J5 as a range name, so it just needs the one formula?

Sample of what I'm trying to achieve.

The company uses Excel 2000 so limited to the number of nested statements.

use INDIRECT, so change your reference to J5 to be INDIRECT(J5)

note indirect is volatile, see here for more info:
http://www.decisionmodels.com/calcsecretsi.htm

You could do the same thing using INDEX and thus avoiding INDIRECT, eg using your example:

=INDEX(A2:H7,MATCH(A7,A2:A7,0),MATCH(J5,A2:H2,0))
or using your PN range which includes all of A
=INDEX(A1:H7,MATCH(A7,PN,0),MATCH(J5,A2:H2,0))

Obviously the reference to A7 in the match is pointless so I'm assuming that will be replaced by a variable.

You would need to use INDIRECT for the value in J5 to be used as a name

=INDEX(INDIRECT(J5),MATCH(A7,PN,0))