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

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" <(E-Mail Removed).(donotspam)> wrote in message
news:1DE5D531-475F-4926-BFE7-(E-Mail Removed)...
>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

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" <(E-Mail Removed).(donotspam)> wrote in message
> news:1DE5D531-475F-4926-BFE7-(E-Mail Removed)...
> >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
>
>
>

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" <(E-Mail Removed).(donotspam)> wrote in message
news:495D1B43-2773-46F8-90E3-(E-Mail Removed)...
>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" <(E-Mail Removed).(donotspam)> wrote in message
>> news:1DE5D531-475F-4926-BFE7-(E-Mail Removed)...
>> >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
>>
>>
>>

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" <(E-Mail Removed).(donotspam)> wrote in message
> news:495D1B43-2773-46F8-90E3-(E-Mail Removed)...
> >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" <(E-Mail Removed).(donotspam)> wrote in message
> >> news:1DE5D531-475F-4926-BFE7-(E-Mail Removed)...
> >> >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
> >>
> >>
> >>
>
>
>