| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 +-------------------------------------------------------------------+ |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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum Based on Checkboxes
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum Based on Checkboxes
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" wrote in message ... 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum Based on Checkboxes
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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum Based on Checkboxes
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum Based on Checkboxes
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum Based on Checkboxes
You're welcome. Thanks for the feedback!
Biff "Vassago" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I return a value based on multiple possible conditions? | Excel Worksheet Functions | |||
| Create formula that will pull a value based on text in diff cell? | Excel Discussion (Misc queries) | |||
| Ranking based on two columns | Excel Worksheet Functions | |||
| Calculating an average based on 2 and 3 criteria | Excel Worksheet Functions | |||
| Subtract a group of cells from a total based on ending date | Excel Discussion (Misc queries) | |||
Linear Mode
