Referencing Named Ranges using values in a cell
 

I'm wondering if anyone knows of way to reference Named Ranges in functions,
but in a way that makes the reference a variable based on values in another
cell.

For example, say there are four named Ranges in the spreadsheet
StateView
CustomerView
SalespersonView
FY2005

What I'm hoping to do is use the sumif function that will change the named
range based on the value in cell A1.
=sumif(NamedRange1,B1,FY2005)

Cell A1 = Variable that will define the View
Cell B1 = Variable that will define the sub-View (the specific state,
customer, or salesperson based on the value in Cell A1)

I realize this is probably better done in a database and I can think of a
number of ways this can be done differently (Pivot Tables, other formulas,
Access). However, this would be preferable because it will be easier for
those who will be using the file to understand/edit and the amount of data
doesn't warrant too much development time.

Thanks in advance for any help


Here's an example of the data
StateView - CustomerView - SalespersonView - FY2005
CA - Cust123 - Bob - 2500
CA - Cust123 - Bob - 1000
CA - Cust234 - Sales - 5000
NV - Cust123 - Sales - 4000

Referencing Named Ranges using values in a cell
 

Do you mean

=SUMIF(INDIRECT("A1"),B1,FY2005)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"eggman" wrote in message
...

I'm wondering if anyone knows of way to reference Named Ranges in
functions,
but in a way that makes the reference a variable based on values in
another
cell.

For example, say there are four named Ranges in the spreadsheet
StateView
CustomerView
SalespersonView
FY2005

What I'm hoping to do is use the sumif function that will change the named
range based on the value in cell A1.
=sumif(NamedRange1,B1,FY2005)

Cell A1 = Variable that will define the View
Cell B1 = Variable that will define the sub-View (the specific state,
customer, or salesperson based on the value in Cell A1)

I realize this is probably better done in a database and I can think of a
number of ways this can be done differently (Pivot Tables, other formulas,
Access). However, this would be preferable because it will be easier for
those who will be using the file to understand/edit and the amount of data
doesn't warrant too much development time.

Thanks in advance for any help


Here's an example of the data
StateView - CustomerView - SalespersonView - FY2005
CA - Cust123 - Bob - 2500
CA - Cust123 - Bob - 1000
CA - Cust234 - Sales - 5000
NV - Cust123 - Sales - 4000

Referencing Named Ranges using values in a cell
 

Thanks much - I was just going to post that I found it. Amazing how you can
look forever and as soon as you ask the question, you find the answer.

"Bob Phillips" wrote:

Do you mean

=SUMIF(INDIRECT("A1"),B1,FY2005)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"eggman" wrote in message
...

I'm wondering if anyone knows of way to reference Named Ranges in
functions,
but in a way that makes the reference a variable based on values in
another
cell.

For example, say there are four named Ranges in the spreadsheet
StateView
CustomerView
SalespersonView
FY2005

What I'm hoping to do is use the sumif function that will change the named
range based on the value in cell A1.
=sumif(NamedRange1,B1,FY2005)

Cell A1 = Variable that will define the View
Cell B1 = Variable that will define the sub-View (the specific state,
customer, or salesperson based on the value in Cell A1)

I realize this is probably better done in a database and I can think of a
number of ways this can be done differently (Pivot Tables, other formulas,
Access). However, this would be preferable because it will be easier for
those who will be using the file to understand/edit and the amount of data
doesn't warrant too much development time.

Thanks in advance for any help


Here's an example of the data
StateView - CustomerView - SalespersonView - FY2005
CA - Cust123 - Bob - 2500
CA - Cust123 - Bob - 1000
CA - Cust234 - Sales - 5000
NV - Cust123 - Sales - 4000