Cell Reference in Function

A

astrodon

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
 
B

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?
 
B

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
 
A

astrodon

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

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top