Toggle values with SUMPRODUCT

[Guest]

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

 

[Guest]

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 here:
http://www.mrexcel.com/td0037.html

Dave

 

[Bob Phillips]

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)

 

[Guest]

Thanks for the suggestion Dave, but it just gives me a #VALUE error. Any more
ideas?

Alan

 

[Guest]

Looks like we have a winner!!! Thanks Bob.

Alan

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)

 

[Guest]

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)

 

[Guest]

Thanks for that - the spinner is a neat touch, adds a bit of flare to the
worksheet!

Alan

 

[Guest]

you are welcome...
i have been awaken by you base on what i readregards