Hello! I'm hoping that this is not the default behavior of the Custom Views function, and that I have instead done something wrong.
I am working on a budgeting spreadsheet. I already have the version I use and have used for several years but many people have said they liked it, and have asked for a copy. So I decided to make a blank version I could pass to anyone who wanted it. People appreciated it but some people had more expenses than me, or in different categories. So, I added more expense lines, but now there are a ton of extra lines that most people won't use - 150 rows that most people will only use a fraction of.
Custom Views seemed like a great solution - make one view that has no filter applied, make a second view that filters out lines with a value of $0.00, and let the recipient switch between them. But the results doesn't play out that way. The blank sheet has some dummy data to illustrate how to use the chart:
The real sheet is much more complicated of course but this will illustrate what is happening. So, I applied filtering to the second column, unchecked $0.00 from the list, and the new table is displayed accordingly.
So far so good so I made a custom view and gave it a name. Now, suppose the recipient gets the chart, and modifies it to suit their expenses. They like the default values but they add a line item for gas in their car, and one for entertainment.
When they apply the view I created before, this is the result:
It took me a while to troubleshoot that what Excel is doing is not applying the same filter I created for the view - "Filter column 2 to show all values except $0.00". Rather it is applying the filter, "Show the values $800.00, $150.00, $20.00, $115.00, and $25.00". It's a subtle distinction but, obviously, displays an incorrect result. The Gasoline line shows properly because it happened to match one of the values in the list already but any other value will not.
Is there a way to tell Excel precisely the criteria that I want to use and, if not, is there a better way to accomplish what I am trying to accomplish?
I am working on a budgeting spreadsheet. I already have the version I use and have used for several years but many people have said they liked it, and have asked for a copy. So I decided to make a blank version I could pass to anyone who wanted it. People appreciated it but some people had more expenses than me, or in different categories. So, I added more expense lines, but now there are a ton of extra lines that most people won't use - 150 rows that most people will only use a fraction of.
Custom Views seemed like a great solution - make one view that has no filter applied, make a second view that filters out lines with a value of $0.00, and let the recipient switch between them. But the results doesn't play out that way. The blank sheet has some dummy data to illustrate how to use the chart:
| Housing | $800.00 |
| Groceries | $150.00 |
| Water | $20.00 |
| Electricity | $115.00 |
| Natural Gas | $25.00 |
| Enter Other Expense Here | $0.00 |
| Enter Other Expense Here | $0.00 |
| Enter Other Expense Here | $0.00 |
| Enter Other Expense Here | $0.00 |
| Enter Other Expense Here | $0.00 |
The real sheet is much more complicated of course but this will illustrate what is happening. So, I applied filtering to the second column, unchecked $0.00 from the list, and the new table is displayed accordingly.
| Housing | $800.00 |
| Groceries | $150.00 |
| Water | $20.00 |
| Electricity | $115.00 |
| Natural Gas | $25.00 |
So far so good so I made a custom view and gave it a name. Now, suppose the recipient gets the chart, and modifies it to suit their expenses. They like the default values but they add a line item for gas in their car, and one for entertainment.
| Housing | $800.00 |
| Groceries | $150.00 |
| Water | $20.00 |
| Electricity | $115.00 |
| Natural Gas | $25.00 |
| Gasoline | $115.00 |
| Entertainment | $100.00 |
| Enter Other Expense Here | $0.00 |
| Enter Other Expense Here | $0.00 |
| Enter Other Expense Here | $0.00 |
When they apply the view I created before, this is the result:
| Housing | $800.00 |
| Groceries | $150.00 |
| Water | $20.00 |
| Electricity | $115.00 |
| Natural Gas | $25.00 |
| Gasoline | $115.00 |
It took me a while to troubleshoot that what Excel is doing is not applying the same filter I created for the view - "Filter column 2 to show all values except $0.00". Rather it is applying the filter, "Show the values $800.00, $150.00, $20.00, $115.00, and $25.00". It's a subtle distinction but, obviously, displays an incorrect result. The Gasoline line shows properly because it happened to match one of the values in the list already but any other value will not.
Is there a way to tell Excel precisely the criteria that I want to use and, if not, is there a better way to accomplish what I am trying to accomplish?
