How can I add a filter button to data on the same worksheet?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Data-validation/Filter Button
0
Answers
0
Selected Answer
Isaac
If you use filtering, you don't really need to use data validation too.
In the revision to your workbook attached, on sheet Objective, I selected A2:A6 then applied the filter (Home ribbon/Editing/Sort & Filter/Filter). A2 then has a down arrow for the filter which shows the values in the the range. I changed some data (A10:A16) and the filter list changed automatically. You can then untick SelectAll on the filter and tick the period you want to be displayed.
You might use the SUM function to add up values but that doesn't change when data is filtered. Instead you should use SUBTOTAL which works on only visible data (AND option 9 is SUM). The formula in H18 Revenue is:
=SUBTOTAL(9,H3:H16)Cells I18 and L18 are calculated from H18 and K18. I have no idea what you want to appear in column J!
If you filter the data, you'll see these totals change to suit.
I've applied the same methods to the Data sheet (and frozen panes for ease of viewing) and left it filtered for "Qtr 2".
Hope this is what you want.
Discussion
HI John,
Thanks for coming back to me, I tried the above method and it will work, my only concern is the list will grow longer from Qtr 1 to Qtr 4. Remember when we are doing forecast the actuals from Qtr 1 to Qtr 2,will form the date for Qtr 3, and Qtr 1 to Qtr2 will be historic. So I filter Qtr 3 it will be show me the full year view for the year and Qtr 0-Qtr 2 will be historic on the same sheet and length depending on the individual data for each Qtr. How can we proceed.
Thanks
I
Thanks for coming back to me, I tried the above method and it will work, my only concern is the list will grow longer from Qtr 1 to Qtr 4. Remember when we are doing forecast the actuals from Qtr 1 to Qtr 2,will form the date for Qtr 3, and Qtr 1 to Qtr2 will be historic. So I filter Qtr 3 it will be show me the full year view for the year and Qtr 0-Qtr 2 will be historic on the same sheet and length depending on the individual data for each Qtr. How can we proceed.
Thanks
I
siehartley2001 (rep: 14) Jun 7, '21 at 7:49 am
Isaac
Thanks for selecting my answer.
You say "Remember when we are doing forecast..." but I can't remember what you haven't told me! (You made no mention of that in your question- which I answered correctly hopefully).
Not sure I understand your concern- data will grow during the year but in Q4, say, you could filter to show data Q1, Q2 and Q3- just untick Q4 in the cleared filter (if you feel that aggregate data is a better basis for a forecast that the previous quarter or Q2 and Q3).
I've seen such sheets with years of data (but you can add that to your filter cells or add a separate column for year and filter that too).
Incidentally, if you need to remove the effects of outliers (say large, unrepeatable but very profitable orders which would skew the forecast) you could hide them manually and have them excluded from the subtotals too by changing the option to 109 so e.g.:
Thanks for selecting my answer.
You say "Remember when we are doing forecast..." but I can't remember what you haven't told me! (You made no mention of that in your question- which I answered correctly hopefully).
Not sure I understand your concern- data will grow during the year but in Q4, say, you could filter to show data Q1, Q2 and Q3- just untick Q4 in the cleared filter (if you feel that aggregate data is a better basis for a forecast that the previous quarter or Q2 and Q3).
I've seen such sheets with years of data (but you can add that to your filter cells or add a separate column for year and filter that too).
Incidentally, if you need to remove the effects of outliers (say large, unrepeatable but very profitable orders which would skew the forecast) you could hide them manually and have them excluded from the subtotals too by changing the option to 109 so e.g.:
=SUBTOTAL(109,H3:H16)
John_Ru (rep: 6142) Jun 7, '21 at 8:42 am
Hi John,
I found one video from your team-Excel Date Filter Magic which is very similar to what am trying to acheive. my concern is on my attached template from A -L these inputs are coming from a lookup from other sheet so for me to add a filter as lookup from a data sheet isn't possible because this is the data set i actually want to filter.
Are there any work around?
I found one video from your team-Excel Date Filter Magic which is very similar to what am trying to acheive. my concern is on my attached template from A -L these inputs are coming from a lookup from other sheet so for me to add a filter as lookup from a data sheet isn't possible because this is the data set i actually want to filter.
Are there any work around?
siehartley2001 (rep: 14) Jul 22, '21 at 6:47 am
Hi Isaac
I haven't seen that video (but it will almost certainbly be from Don who owns the TeachExcel site). Please create a new question and hopefully Don or one of us will reply.
I haven't seen that video (but it will almost certainbly be from Don who owns the TeachExcel site). Please create a new question and hopefully Don or one of us will reply.
John_Ru (rep: 6142) Jul 22, '21 at 7:04 am
Isaac
I looked at the video and it could be used in your case, either (more simply) with your Qtr 0, Qtr 1 etc or with calendar dates per entry (as the video). Please clarify your requirement in a new question and use LINK to point to the video (so others can answer or follow the thread).
I looked at the video and it could be used in your case, either (more simply) with your Qtr 0, Qtr 1 etc or with calendar dates per entry (as the video). Please clarify your requirement in a new question and use LINK to point to the video (so others can answer or follow the thread).
John_Ru (rep: 6142) Jul 22, '21 at 8:26 am
I've created a new question.Thanks John,
siehartley2001 (rep: 14) Jul 25, '21 at 9:26 am