Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Toggle values with SUMPRODUCT
Hi to all,
I am trying to build a basic Balance Sheet to use as a reference for another project. One worksheet contains all the data for each month of 2006 (monthly ending balances), and I am using the following to pull it into my template: =SUMPRODUCT(--(Region=E$11),--(Group=$D17),January) Where Region is the region the office is in, Group is the account group the balance belongs to (Cash, A/R etc.) and January is the month to show (all ranges are named). Is there a way I can setup some form of toggle for the month , so I can switch between each of the 12 months, and not have to change formulae or create duplicate worksheets? All help appreciated. Regards, Alan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Toggle values with SUMPRODUCT
Use data validation to create a drop-down of the 12 months in a cell and
reference that cell in your SUMPRODUCT function. So, if you want to change from January to February, you would select February from the drop-down, and your calculations would adjust automatically. Data validation can be found via Data--Validation. For more info, see he http://www.mrexcel.com/td0037.html Dave -- Brevity is the soul of wit. "Alan Smith" wrote: Hi to all, I am trying to build a basic Balance Sheet to use as a reference for another project. One worksheet contains all the data for each month of 2006 (monthly ending balances), and I am using the following to pull it into my template: =SUMPRODUCT(--(Region=E$11),--(Group=$D17),January) Where Region is the region the office is in, Group is the account group the balance belongs to (Cash, A/R etc.) and January is the month to show (all ranges are named). Is there a way I can setup some form of toggle for the month , so I can switch between each of the 12 months, and not have to change formulae or create duplicate worksheets? All help appreciated. Regards, Alan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Toggle values with SUMPRODUCT
Put the name in a cell, say A1, and use
=SUMPRODUCT(--(Region=E$11),--(Group=$D17),INDIRECT(A1)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alan Smith" wrote in message ... Hi to all, I am trying to build a basic Balance Sheet to use as a reference for another project. One worksheet contains all the data for each month of 2006 (monthly ending balances), and I am using the following to pull it into my template: =SUMPRODUCT(--(Region=E$11),--(Group=$D17),January) Where Region is the region the office is in, Group is the account group the balance belongs to (Cash, A/R etc.) and January is the month to show (all ranges are named). Is there a way I can setup some form of toggle for the month , so I can switch between each of the 12 months, and not have to change formulae or create duplicate worksheets? All help appreciated. Regards, Alan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Toggle values with SUMPRODUCT
Thanks for the suggestion Dave, but it just gives me a #VALUE error. Any more
ideas? Alan "Dave F" wrote: Use data validation to create a drop-down of the 12 months in a cell and reference that cell in your SUMPRODUCT function. So, if you want to change from January to February, you would select February from the drop-down, and your calculations would adjust automatically. Data validation can be found via Data--Validation. For more info, see he http://www.mrexcel.com/td0037.html Dave -- Brevity is the soul of wit. "Alan Smith" wrote: Hi to all, I am trying to build a basic Balance Sheet to use as a reference for another project. One worksheet contains all the data for each month of 2006 (monthly ending balances), and I am using the following to pull it into my template: =SUMPRODUCT(--(Region=E$11),--(Group=$D17),January) Where Region is the region the office is in, Group is the account group the balance belongs to (Cash, A/R etc.) and January is the month to show (all ranges are named). Is there a way I can setup some form of toggle for the month , so I can switch between each of the 12 months, and not have to change formulae or create duplicate worksheets? All help appreciated. Regards, Alan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Toggle values with SUMPRODUCT
Looks like we have a winner!!! Thanks Bob.
Alan "Bob Phillips" wrote: Put the name in a cell, say A1, and use =SUMPRODUCT(--(Region=E$11),--(Group=$D17),INDIRECT(A1)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alan Smith" wrote in message ... Hi to all, I am trying to build a basic Balance Sheet to use as a reference for another project. One worksheet contains all the data for each month of 2006 (monthly ending balances), and I am using the following to pull it into my template: =SUMPRODUCT(--(Region=E$11),--(Group=$D17),January) Where Region is the region the office is in, Group is the account group the balance belongs to (Cash, A/R etc.) and January is the month to show (all ranges are named). Is there a way I can setup some form of toggle for the month , so I can switch between each of the 12 months, and not have to change formulae or create duplicate worksheets? All help appreciated. Regards, Alan |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Toggle values with SUMPRODUCT
u may also try to take advantage of a Spinner from the Form tools (format
control with min(1), max(12)) to toggle a cell, let say B1 which is the Spinners Cell link. then your formula remains something like this for toggling results. =SUMPRODUCT(--(Region=E$11),--(Group=$D17),B1) -- ***** birds of the same feather flock together.. "Alan Smith" wrote: Hi to all, I am trying to build a basic Balance Sheet to use as a reference for another project. One worksheet contains all the data for each month of 2006 (monthly ending balances), and I am using the following to pull it into my template: =SUMPRODUCT(--(Region=E$11),--(Group=$D17),January) Where Region is the region the office is in, Group is the account group the balance belongs to (Cash, A/R etc.) and January is the month to show (all ranges are named). Is there a way I can setup some form of toggle for the month , so I can switch between each of the 12 months, and not have to change formulae or create duplicate worksheets? All help appreciated. Regards, Alan |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Toggle values with SUMPRODUCT
Thanks for that - the spinner is a neat touch, adds a bit of flare to the
worksheet! Alan "driller" wrote: u may also try to take advantage of a Spinner from the Form tools (format control with min(1), max(12)) to toggle a cell, let say B1 which is the Spinners Cell link. then your formula remains something like this for toggling results. =SUMPRODUCT(--(Region=E$11),--(Group=$D17),B1) -- ***** birds of the same feather flock together.. "Alan Smith" wrote: Hi to all, I am trying to build a basic Balance Sheet to use as a reference for another project. One worksheet contains all the data for each month of 2006 (monthly ending balances), and I am using the following to pull it into my template: =SUMPRODUCT(--(Region=E$11),--(Group=$D17),January) Where Region is the region the office is in, Group is the account group the balance belongs to (Cash, A/R etc.) and January is the month to show (all ranges are named). Is there a way I can setup some form of toggle for the month , so I can switch between each of the 12 months, and not have to change formulae or create duplicate worksheets? All help appreciated. Regards, Alan |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Toggle values with SUMPRODUCT
you are welcome...
i have been awaken by you base on what i read Is there a way I can setup some form of toggle for the month , so I can switch between each of the 12 months, and not have to change formulae or create duplicate worksheets? All help appreciated. regards -- ***** birds of the same feather flock together.. "Alan Smith" wrote: Thanks for that - the spinner is a neat touch, adds a bit of flare to the worksheet! Alan "driller" wrote: u may also try to take advantage of a Spinner from the Form tools (format control with min(1), max(12)) to toggle a cell, let say B1 which is the Spinners Cell link. then your formula remains something like this for toggling results. =SUMPRODUCT(--(Region=E$11),--(Group=$D17),B1) -- ***** birds of the same feather flock together.. "Alan Smith" wrote: Hi to all, I am trying to build a basic Balance Sheet to use as a reference for another project. One worksheet contains all the data for each month of 2006 (monthly ending balances), and I am using the following to pull it into my template: =SUMPRODUCT(--(Region=E$11),--(Group=$D17),January) Where Region is the region the office is in, Group is the account group the balance belongs to (Cash, A/R etc.) and January is the month to show (all ranges are named). Is there a way I can setup some form of toggle for the month , so I can switch between each of the 12 months, and not have to change formulae or create duplicate worksheets? All help appreciated. Regards, Alan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average only first number of actual values | Excel Worksheet Functions | |||
How to Search, Count, Match and Post Values | Excel Worksheet Functions | |||
URGENT: Please Advise. SumProduct and Operand Question | Excel Discussion (Misc queries) | |||
Obtaining Max and Min Values with Sumproduct | Excel Discussion (Misc queries) | |||
Toggle switch between absolute and relative values creating a macr | Excel Discussion (Misc queries) |