software

I have give names to various lists - lets say it is
quot;Locationquot; =Sheet1!$A$2:$A$108.
quot;Viewquot; =Sheet1!$B$2:$B$108
quot;PropClassquot; =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, { quot;Locationquot;, quot;Viewquot;, quot;PropClassquot;, ... }

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 emailquot;astrodonquot; .(donotspam)gt; wrote in message
...
gt;I have give names to various lists - lets say it is
gt; quot;Locationquot; =Sheet1!$A$2:$A$108.
gt; quot;Viewquot; =Sheet1!$B$2:$B$108
gt; quot;PropClassquot; =Sheet1!$C$2:$C$108
gt; etc.
gt;
gt; Now lets also say I have a need to be able to reproduce that list on
gt; another
gt; sheet by using Data Validation | List in cell Sheet5!C1. The list being
gt; of
gt; course, { quot;Locationquot;, quot;Viewquot;, quot;PropClassquot;, ... }
gt;
gt; Now I have placed the followng formula in cells Sheet5!C2:C108:
gt;
gt; =INDEX($C$1, Row(), 0) Which returns a #REF error. However, typing
gt; =INDEX(Location, ROW(), 0) gives the desired result. Why will the
gt; INDEX(...)
gt; function not work using a cell reference?
gt;
gt; Is there a workaround?
gt;
gt; TIA
gt; astrodon
gt; --
gt; 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 endquot;Bernard Liengmequot; wrote:

gt; The INDEX function in =INDEX($C$1, Row(), 0) is looking for an entry in an
gt; array which has one entry (C1)
gt; Normally one uses a range as in =INDEX($C$1:$D$20, Row(), 0)
gt; any help?
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt;
gt; quot;astrodonquot; .(donotspam)gt; wrote in message
gt; ...
gt; gt;I have give names to various lists - lets say it is
gt; gt; quot;Locationquot; =Sheet1!$A$2:$A$108.
gt; gt; quot;Viewquot; =Sheet1!$B$2:$B$108
gt; gt; quot;PropClassquot; =Sheet1!$C$2:$C$108
gt; gt; etc.
gt; gt;
gt; gt; Now lets also say I have a need to be able to reproduce that list on
gt; gt; another
gt; gt; sheet by using Data Validation | List in cell Sheet5!C1. The list being
gt; gt; of
gt; gt; course, { quot;Locationquot;, quot;Viewquot;, quot;PropClassquot;, ... }
gt; gt;
gt; gt; Now I have placed the followng formula in cells Sheet5!C2:C108:
gt; gt;
gt; gt; =INDEX($C$1, Row(), 0) Which returns a #REF error. However, typing
gt; gt; =INDEX(Location, ROW(), 0) gives the desired result. Why will the
gt; gt; INDEX(...)
gt; gt; function not work using a cell reference?
gt; gt;
gt; gt; Is there a workaround?
gt; gt;
gt; gt; TIA
gt; gt; astrodon
gt; gt; --
gt; gt; The writing of books there is no end
gt;
gt;
gt;

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

quot;astrodonquot; .(donotspam)gt; wrote in message
...
gt;A range is being referenced to, ie a NAMED range such as Location, etc
gt; --
gt; The writing of books there is no end
gt;
gt;
gt; quot;Bernard Liengmequot; wrote:
gt;
gt;gt; The INDEX function in =INDEX($C$1, Row(), 0) is looking for an entry in
gt;gt; an
gt;gt; array which has one entry (C1)
gt;gt; Normally one uses a range as in =INDEX($C$1:$D$20, Row(), 0)
gt;gt; any help?
gt;gt; --
gt;gt; Bernard V Liengme
gt;gt; www.stfx.ca/people/bliengme
gt;gt; remove caps from email
gt;gt;
gt;gt;
gt;gt; quot;astrodonquot; .(donotspam)gt; wrote in message
gt;gt; ...
gt;gt; gt;I have give names to various lists - lets say it is
gt;gt; gt; quot;Locationquot; =Sheet1!$A$2:$A$108.
gt;gt; gt; quot;Viewquot; =Sheet1!$B$2:$B$108
gt;gt; gt; quot;PropClassquot; =Sheet1!$C$2:$C$108
gt;gt; gt; etc.
gt;gt; gt;
gt;gt; gt; Now lets also say I have a need to be able to reproduce that list on
gt;gt; gt; another
gt;gt; gt; sheet by using Data Validation | List in cell Sheet5!C1. The list
gt;gt; gt; being
gt;gt; gt; of
gt;gt; gt; course, { quot;Locationquot;, quot;Viewquot;, quot;PropClassquot;, ... }
gt;gt; gt;
gt;gt; gt; Now I have placed the followng formula in cells Sheet5!C2:C108:
gt;gt; gt;
gt;gt; gt; =INDEX($C$1, Row(), 0) Which returns a #REF error. However, typing
gt;gt; gt; =INDEX(Location, ROW(), 0) gives the desired result. Why will the
gt;gt; gt; INDEX(...)
gt;gt; gt; function not work using a cell reference?
gt;gt; gt;
gt;gt; gt; Is there a workaround?
gt;gt; gt;
gt;gt; gt; TIA
gt;gt; gt; astrodon
gt;gt; gt; --
gt;gt; gt; The writing of books there is no end
gt;gt;
gt;gt;
gt;gt;
Yeah. Well anyway INDIRECT(Ref) works just fine so don't need
INDEX(criiteria, array,column)

Thanks
--
The writing of books there is no endquot;Bernard Liengmequot; wrote:

gt; I was referring to the formula without a named range just a single cell C1
gt; QUOTE: =INDEX($C$1, Row(), 0) Which returns a #REF error
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;astrodonquot; .(donotspam)gt; wrote in message
gt; ...
gt; gt;A range is being referenced to, ie a NAMED range such as Location, etc
gt; gt; --
gt; gt; The writing of books there is no end
gt; gt;
gt; gt;
gt; gt; quot;Bernard Liengmequot; wrote:
gt; gt;
gt; gt;gt; The INDEX function in =INDEX($C$1, Row(), 0) is looking for an entry in
gt; gt;gt; an
gt; gt;gt; array which has one entry (C1)
gt; gt;gt; Normally one uses a range as in =INDEX($C$1:$D$20, Row(), 0)
gt; gt;gt; any help?
gt; gt;gt; --
gt; gt;gt; Bernard V Liengme
gt; gt;gt; www.stfx.ca/people/bliengme
gt; gt;gt; remove caps from email
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;astrodonquot; .(donotspam)gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I have give names to various lists - lets say it is
gt; gt;gt; gt; quot;Locationquot; =Sheet1!$A$2:$A$108.
gt; gt;gt; gt; quot;Viewquot; =Sheet1!$B$2:$B$108
gt; gt;gt; gt; quot;PropClassquot; =Sheet1!$C$2:$C$108
gt; gt;gt; gt; etc.
gt; gt;gt; gt;
gt; gt;gt; gt; Now lets also say I have a need to be able to reproduce that list on
gt; gt;gt; gt; another
gt; gt;gt; gt; sheet by using Data Validation | List in cell Sheet5!C1. The list
gt; gt;gt; gt; being
gt; gt;gt; gt; of
gt; gt;gt; gt; course, { quot;Locationquot;, quot;Viewquot;, quot;PropClassquot;, ... }
gt; gt;gt; gt;
gt; gt;gt; gt; Now I have placed the followng formula in cells Sheet5!C2:C108:
gt; gt;gt; gt;
gt; gt;gt; gt; =INDEX($C$1, Row(), 0) Which returns a #REF error. However, typing
gt; gt;gt; gt; =INDEX(Location, ROW(), 0) gives the desired result. Why will the
gt; gt;gt; gt; INDEX(...)
gt; gt;gt; gt; function not work using a cell reference?
gt; gt;gt; gt;
gt; gt;gt; gt; Is there a workaround?
gt; gt;gt; gt;
gt; gt;gt; gt; TIA
gt; gt;gt; gt; astrodon
gt; gt;gt; gt; --
gt; gt;gt; gt; The writing of books there is no end
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;