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

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

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

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

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

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

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

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