Cell Reference in Function
 

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
--
The writing of books there is no end

Cell Reference in Function
 

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?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"astrodon" .(donotspam) wrote in message
...
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
--
The writing of books there is no end

Cell Reference in Function
 

A range is being referenced to, ie a NAMED range such as Location, etc
--
The writing of books there is no end


"Bernard Liengme" wrote:

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?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"astrodon" .(donotspam) wrote in message
...
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
--
The writing of books there is no end

Cell Reference in Function
 

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"astrodon" .(donotspam) wrote in message
...
A range is being referenced to, ie a NAMED range such as Location, etc
--
The writing of books there is no end


"Bernard Liengme" wrote:

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?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"astrodon" .(donotspam) wrote in message
...
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
--
The writing of books there is no end

Cell Reference in Function
 

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

Thanks
--
The writing of books there is no end


"Bernard Liengme" wrote:

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"astrodon" .(donotspam) wrote in message
...
A range is being referenced to, ie a NAMED range such as Location, etc
--
The writing of books there is no end


"Bernard Liengme" wrote:

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?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"astrodon" .(donotspam) wrote in message
...
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
--
The writing of books there is no end