Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Referencing defined range
Hello,
I have three named ranges (percentSales,percentMaterial,percentLabor). When a user types into an account cell and wants to retrieve information for percentSales, they would type "Sales". How can I take this input into a function and refer to the percentSales range. I tried percent&"Sales", but this is not working. It errors out because it is not refering to the range, but just the text "percentSales". Below is an example of what I am doing. What can I do to take the input and refer to the named range. What I would like to happen =INDEX(percentSales,MATCH(A1&C1&B1,rangeEntity&ran geMonth&rangeCCY,0)) What is happening =INDEX("percentSales",MATCH(A1&C1&B1,rangeEntity&r angeMonth&rangeCCY,0)) Thanks, John |
#2
|
|||
|
|||
Forget the quotation marks.
To add cells A1 through A6 we use =sum(A1:A6) If we defined A1:A6 as example1, then =SUM(example1) will work, not =SUM("example1") -- Gary''s Student "spartanmba" wrote: Hello, I have three named ranges (percentSales,percentMaterial,percentLabor). When a user types into an account cell and wants to retrieve information for percentSales, they would type "Sales". How can I take this input into a function and refer to the percentSales range. I tried percent&"Sales", but this is not working. It errors out because it is not refering to the range, but just the text "percentSales". Below is an example of what I am doing. What can I do to take the input and refer to the named range. What I would like to happen =INDEX(percentSales,MATCH(A1&C1&B1,rangeEntity&ran geMonth&rangeCCY,0)) What is happening =INDEX("percentSales",MATCH(A1&C1&B1,rangeEntity&r angeMonth&rangeCCY,0)) Thanks, John |
#3
|
|||
|
|||
The issue is I am trying to dynamically refer to the defined name. If you
use evaluate formula on the audit toolbar you will see that instead of seeing a dyamically referenced range you will see just text. "Gary''s Student" wrote: Forget the quotation marks. To add cells A1 through A6 we use =sum(A1:A6) If we defined A1:A6 as example1, then =SUM(example1) will work, not =SUM("example1") -- Gary''s Student "spartanmba" wrote: Hello, I have three named ranges (percentSales,percentMaterial,percentLabor). When a user types into an account cell and wants to retrieve information for percentSales, they would type "Sales". How can I take this input into a function and refer to the percentSales range. I tried percent&"Sales", but this is not working. It errors out because it is not refering to the range, but just the text "percentSales". Below is an example of what I am doing. What can I do to take the input and refer to the named range. What I would like to happen =INDEX(percentSales,MATCH(A1&C1&B1,rangeEntity&ran geMonth&rangeCCY,0)) What is happening =INDEX("percentSales",MATCH(A1&C1&B1,rangeEntity&r angeMonth&rangeCCY,0)) Thanks, John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#VALUE! On An Array Formula Referencing a Range Outside The Workbo | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions | |||
Defined range using more than one column | Excel Discussion (Misc queries) | |||
Defined range problem | Excel Discussion (Misc queries) | |||
Defined range difficulty | Excel Discussion (Misc queries) |