• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Daily Expenses in Excel- with Filter problem.

fundi

New Member
Hello guys,

Please find attached an EXCEL I created for Daily Expenses / Outputs and Daily Sales/Inputs.

Unfortunately I can't use the FILTER properly.

-If you try to filter BANK category to show only, it also includes Cash.


-If you filter CASH category to show only, it also include the BANK.

So basically, I am not getting the END BALANCE figure :(
Where am I wrong ?
 

Attachments

Peter Bartholomew

Well-Known Member
I suggest you put the Expenses and Sales tables on separate worksheets.
In future you may be able to use a FILTER function that will create a separate copy of the filtered data [Office 365 only].
Advanced filter / extract will also do that.
 

fundi

New Member
I suggest you put the Expenses and Sales tables on separate worksheets.
In future you may be able to use a FILTER function that will create a separate copy of the filtered data [Office 365 only].
Advanced filter / extract will also do that.
Thanks for your reply. If I separate Expenses and Sales in separate worksheets, will get the ending balance ?
 

Peter Bartholomew

Well-Known Member
Unless you have good reason to do otherwise, name the Tables and use the built-in table functionality to display a Totals row for each table.
The balance is then given by
= Sales[[#Totals],[Amount]] - Expenses[[#Totals],[Amount]]
As you add data for additional months, the totals row will move down but the formula will still work.
 

fundi

New Member
Unless you have good reason to do otherwise, name the Tables and use the built-in table functionality to display a Totals row for each table.
The balance is then given by
= Sales[[#Totals],[Amount]] - Expenses[[#Totals],[Amount]]
As you add data for additional months, the totals row will move down but the formula will still work.
Thanks for your suggestion. Can you show me how its done? you even edit my excel and resend me.
 

p45cal

Well-Known Member
Alternatively, I suggest removing the subtotals from your tables and summarising them with pivot tables.
See Sheet1 (2) in the attached and pivot tables in columns O and X.
If there were more data you'd see more months.
The pivot tables don't have to be on the same sheet as the data.
Additionally, you have the flexibility to summarise/filter the data in many more ways with a pivot table.
 

Attachments

fundi

New Member
Alternatively, I suggest removing the subtotals from your tables and summarising them with pivot tables.
See Sheet1 (2) in the attached and pivot tables in columns O and X.
If there were more data you'd see more months.
The pivot tables don't have to be on the same sheet as the data.
Additionally, you have the flexibility to summarise/filter the data in many more ways with a pivot table.
Thank for the idea... Although its a good suggestion, but it seems too complicated for me :(
 

fundi

New Member
There is a catch. The dropdown filters now work independently but the group filters need to be synchronised by the user.
Excellent ! :) Thank you so much !

Btw, at the end balance... You have used a formula of = = tblSales[[#Totals],[Amount]] - tblExpenses[[#Totals],[Amount]]

What difference will it make if I just use a simple division formula =Sheet3!E53-E59

again thank you :)
 

Peter Bartholomew

Well-Known Member
I agree that your direct referenced formula is both shorter and likely to be more familiar to you.
Despite that, I believe it would be better to persevere with structured referencing. Most references are not as cumbersome as the ones above. For example tblSales is the entire data area of one of the tables, [amount] is a complete column referenced from within the table and [@amount] is the single value within the current record.

Even from the long formulas I used, it is possible to read that the value is in the Sales table, it is a total and it is within the Amount column. What can you tell about Sheet3!E53 without tracking down the cells in the spreadsheet? The other useful feature of a Table is that the Structured References resize to match the data whereas care needs to be taken that formulas based upon simple ranges are adjusted as necessary.
 

fundi

New Member
I agree that your direct referenced formula is both shorter and likely to be more familiar to you.
Despite that, I believe it would be better to persevere with structured referencing. Most references are not as cumbersome as the ones above. For example tblSales is the entire data area of one of the tables, [amount] is a complete column referenced from within the table and [@amount] is the single value within the current record.

Even from the long formulas I used, it is possible to read that the value is in the Sales table, it is a total and it is within the Amount column. What can you tell about Sheet3!E53 without tracking down the cells in the spreadsheet? The other useful feature of a Table is that the Structured References resize to match the data whereas care needs to be taken that formulas based upon simple ranges are adjusted as necessary.
Thank you for your explanation guess will be using your method :)
 
Top