Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can INDIRECT function reference a cell that contains a formula | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
INDIRECT and Named Ranges referencing closed workbook | Excel Worksheet Functions | |||
Named Ranges - accessing a cell | Excel Discussion (Misc queries) | |||
How to slot cell values into pre-defined ranges | Excel Discussion (Misc queries) |