Sum Based on Checkboxes

Hello,

I have a spreadsheet sample attached below. Basically, I have a sheet with the follow fields:

TOTALS 210 460 45.7%
Toy Color Accts Atts Pass
Ball Blue 20 100 20.0%
Ball Black 30 90 33.3%
Bike Blue 40 70 57.1%
Frisbee Red 60 55 109.1%
Toy Blue 50 60 83.3%
Star Red 10 85 11.8%

What I would like to do is set up an autofilter using checkboxes. I would like to create a series of checkboxes that will show above the sheet each labelled like "Red", "Blue", and "Black" where a user can select either one color, multiple colors, or all colors, and the list will only show the colors specified. I also want it to recalculate the totals based on the checkboxes selected. Does anyone have any ideas as I am stumped for the moment.

Thanks!

Vassago

Hi!

>Does anyone have any ideas

Yes. Why reinvent the wheel?

Autofilter can already do this!

You could link each checkbox to a cell and based on the check state use a
formula in a helper column (hidden) and then autofilter on that helper
column.

Biff

"Vassago" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> I have a spreadsheet sample attached below. Basically, I have a sheet
> with the follow fields:
>
> TOTALS 210 460 45.7%
> Toy Color Accts Atts Pass
> Ball Blue 20 100 20.0%
> Ball Black 30 90 33.3%
> Bike Blue 40 70 57.1%
> Frisbee Red 60 55 109.1%
> Toy Blue 50 60 83.3%
> Star Red 10 85 11.8%
>
> What I would like to do is set up an autofilter using checkboxes. I
> would like to create a series of checkboxes that will show above the
> sheet each labelled like "Red", "Blue", and "Black" where a user can
> select either one color, multiple colors, or all colors, and the list
> will only show the colors specified. I also want it to recalculate the
> totals based on the checkboxes selected. Does anyone have any ideas as
> I am stumped for the moment.
>
> Thanks!
>
> Vassago
>
>
> +-------------------------------------------------------------------+
> |Filename: Sample.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5125 |
> +-------------------------------------------------------------------+
>
> --
> Vassago
> ------------------------------------------------------------------------
> Vassago's Profile:
> http://www.excelforum.com/member.php...o&userid=36906
> View this thread: http://www.excelforum.com/showthread...hreadid=566204
>

>in a helper column (hidden)

Well, forget about the hidden part. It'd be kind of hard to select the
filter from hidden column!

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
>>Does anyone have any ideas
>
> Yes. Why reinvent the wheel?
>
> Autofilter can already do this!
>
> You could link each checkbox to a cell and based on the check state use a
> formula in a helper column (hidden) and then autofilter on that helper
> column.
>
> Biff
>
> "Vassago" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hello,
>>
>> I have a spreadsheet sample attached below. Basically, I have a sheet
>> with the follow fields:
>>
>> TOTALS 210 460 45.7%
>> Toy Color Accts Atts Pass
>> Ball Blue 20 100 20.0%
>> Ball Black 30 90 33.3%
>> Bike Blue 40 70 57.1%
>> Frisbee Red 60 55 109.1%
>> Toy Blue 50 60 83.3%
>> Star Red 10 85 11.8%
>>
>> What I would like to do is set up an autofilter using checkboxes. I
>> would like to create a series of checkboxes that will show above the
>> sheet each labelled like "Red", "Blue", and "Black" where a user can
>> select either one color, multiple colors, or all colors, and the list
>> will only show the colors specified. I also want it to recalculate the
>> totals based on the checkboxes selected. Does anyone have any ideas as
>> I am stumped for the moment.
>>
>> Thanks!
>>
>> Vassago
>>
>>
>> +-------------------------------------------------------------------+
>> |Filename: Sample.zip |
>> |Download: http://www.excelforum.com/attachment.php?postid=5125 |
>> +-------------------------------------------------------------------+
>>
>> --
>> Vassago
>> ------------------------------------------------------------------------
>> Vassago's Profile:
>> http://www.excelforum.com/member.php...o&userid=36906
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=566204
>>
>
>

Thanks for the replies!

Now I'm even more lost than I started.

Can autofilter be used to filter more than one color? I would need this capability. I also though autofilter would not filter totals, just the actual lists. Can you elaborate on this for me please in baby talk? As much as I know in Access I lack in Excel.

Thanks!

Vassago

Originally Posted by Biff

Hi!

>Does anyone have any ideas

Yes. Why reinvent the wheel?

Autofilter can already do this!

You could link each checkbox to a cell and based on the check state use a
formula in a helper column (hidden) and then autofilter on that helper
column.

Biff

"Vassago" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> I have a spreadsheet sample attached below. Basically, I have a sheet
> with the follow fields:
>
> TOTALS 210 460 45.7%
> Toy Color Accts Atts Pass
> Ball Blue 20 100 20.0%
> Ball Black 30 90 33.3%
> Bike Blue 40 70 57.1%
> Frisbee Red 60 55 109.1%
> Toy Blue 50 60 83.3%
> Star Red 10 85 11.8%
>
> What I would like to do is set up an autofilter using checkboxes. I
> would like to create a series of checkboxes that will show above the
> sheet each labelled like "Red", "Blue", and "Black" where a user can
> select either one color, multiple colors, or all colors, and the list
> will only show the colors specified. I also want it to recalculate the
> totals based on the checkboxes selected. Does anyone have any ideas as
> I am stumped for the moment.
>
> Thanks!
>
> Vassago
>
>
> +-------------------------------------------------------------------+
> |Filename: Sample.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5125 |
> +-------------------------------------------------------------------+
>
> --
> Vassago
> ------------------------------------------------------------------------
> Vassago's Profile:
> http://www.excelforum.com/member.php...o&userid=36906
> View this thread: http://www.excelforum.com/showthread...hreadid=566204
>

Ok......

Here's a small sample file:

Checkbox filter.xls 17kb

http://cjoint.com/?hDuiGzsJTz

I used checkboxes from the Forms toolbar.

The checkboxes are linked to the cells directly above them. For example: the
checkbox in B2 is linked to B1, the checkbox in C2 is linked to C1. When you
"check" a checkbox its check state is TRUE. When you "uncheck" a checkbox
its check state is FALSE. The check state is returned to the linked cell.
You can't see the check state because I set the font color of the linked
cells to be the same as the fill color.

Column F is the helper column and contains a formula to determine which
checkboxes have been checked and compares that to the color entry in column
B.

The totals are calculated using the SUBTOTAL function.

An Autofilter has been applied to row 7.

So, check the color(s) you want then filter on "x" in column F.

Biff

"Vassago" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the replies!
>
> Now I'm even more lost than I started.
>
> Can autofilter be used to filter more than one color? I would need
> this capability. I also though autofilter would not filter totals,
> just the actual lists. Can you elaborate on this for me please in baby
> talk? As much as I know in Access I lack in Excel.
>
> Thanks!
>
> Vassago
>
> Biff Wrote:
>> Hi!
>>
>> >Does anyone have any ideas
>>
>> Yes. Why reinvent the wheel?
>>
>> Autofilter can already do this!
>>
>> You could link each checkbox to a cell and based on the check state use
>> a
>> formula in a helper column (hidden) and then autofilter on that helper
>> column.
>>
>> Biff
>>
>> "Vassago" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > Hello,
>> >
>> > I have a spreadsheet sample attached below. Basically, I have a
>> sheet
>> > with the follow fields:
>> >
>> > TOTALS 210 460 45.7%
>> > Toy Color Accts Atts Pass
>> > Ball Blue 20 100 20.0%
>> > Ball Black 30 90 33.3%
>> > Bike Blue 40 70 57.1%
>> > Frisbee Red 60 55 109.1%
>> > Toy Blue 50 60 83.3%
>> > Star Red 10 85 11.8%
>> >
>> > What I would like to do is set up an autofilter using checkboxes. I
>> > would like to create a series of checkboxes that will show above the
>> > sheet each labelled like "Red", "Blue", and "Black" where a user can
>> > select either one color, multiple colors, or all colors, and the
>> list
>> > will only show the colors specified. I also want it to recalculate
>> the
>> > totals based on the checkboxes selected. Does anyone have any ideas
>> as
>> > I am stumped for the moment.
>> >
>> > Thanks!
>> >
>> > Vassago
>> >
>> >
>> >
>> +-------------------------------------------------------------------+
>> > |Filename: Sample.zip
>> |
>> > |Download: http://www.excelforum.com/attachment.php?postid=5125
>> |
>> >
>> +-------------------------------------------------------------------+
>> >
>> > --
>> > Vassago
>> >
>> ------------------------------------------------------------------------
>> > Vassago's Profile:
>> > http://www.excelforum.com/member.php...o&userid=36906
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=566204
>> >
>
>
> --
> Vassago
> ------------------------------------------------------------------------
> Vassago's Profile:
> http://www.excelforum.com/member.php...o&userid=36906
> View this thread: http://www.excelforum.com/showthread...hreadid=566204
>

Awesome! The Subtotal function was the missing link for me. I didn't know that existed.

Thank you so much for your help. I've learned a lot.

You're welcome. Thanks for the feedback!

Biff

"Vassago" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome! The Subtotal function was the missing link for me. I didn't
> know that existed.
>
> Thank you so much for your help. I've learned a lot.
>
>
> --
> Vassago
> ------------------------------------------------------------------------
> Vassago's Profile:
> http://www.excelforum.com/member.php...o&userid=36906
> View this thread: http://www.excelforum.com/showthread...hreadid=566204
>