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

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

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