Cell Reference in Function

[Guest]

I have give names to various lists - lets say it is
"Location" =Sheet1!$A$2:$A$108.
"View" =Sheet1!$B$2:$B$108
"PropClass" =Sheet1!$C$2:$C$108
etc.

Now lets also say I have a need to be able to reproduce that list on another
sheet by using Data Validation | List in cell Sheet5!C1. The list being of
course, { "Location", "View", "PropClass", ... }

Now I have placed the followng formula in cells Sheet5!C2:C108:

=INDEX($C$1, Row(), 0) Which returns a #REF error. However, typing
=INDEX(Location, ROW(), 0) gives the desired result. Why will the INDEX(...)
function not work using a cell reference?

Is there a workaround?

TIA
astrodon

 

[Bernard Liengme]

The INDEX function in =INDEX($C$1, Row(), 0) is looking for an entry in an
array which has one entry (C1)
Normally one uses a range as in =INDEX($C$1:$D$20, Row(), 0)
any help?

 

[Guest]

A range is being referenced to, ie a NAMED range such as Location, etc

 

[Bernard Liengme]

I was referring to the formula without a named range just a single cell C1
QUOTE: =INDEX($C$1, Row(), 0) Which returns a #REF error

 

[Guest]

Yeah. Well anyway INDIRECT(Ref) works just fine so don't need
INDEX(criiteria, array,column)

Thanks