Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use a formula as a cell reference in a function | Excel Worksheet Functions | |||
copying the function contained within a cell to anouther cell. | Excel Worksheet Functions | |||
how do I format a cell reference to move as source changes | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |