Use formulas to filter and sum master data by category

I am trying to create a user-friendly Excel for calculating income and expenses in Excel 2013.

The idea is that in sheet1 the user manually enters all expenses as one long table, and also enters the type of expense: Food, Other, etc. (there is only a number of options). In sheet2 I want the user to automatically view a table displaying only rows with the value "Food". In sheet3 the user should view only rows containing the value "Other", and so on for all values entered in the "Type" field.

I cannot just use a simple filter, firstly because I need to calculate in sheet2 all the food (and not the other lines which are being filtered out). And secondly, the Excel filter cuts out the numbering of hidden lines. (For aesthetic reasons I want the filtered sheet to have its own full numbering.) Thirdly, the filtering should be an automatic, ongoing function as the list in sheet1 gets longer, not by forcing the user to click the filter again.

I know this can be done using a query in Access, but how can this be done in Excel *2013* alone?

Thank you all for your help!

Hi

If you upload a sample booklet of your what you are trying to achieve and show your desired outcome of sheet 2 too, this would help.

It sounds like you want individual tabs per expense, which you could use Pivots or FILTER formula.

Hello kloppy and Welcome to Excel Forum.
See if the Show Report Filter Pages method discussed in the tutorial video linked below might work for your situation.
https://www.youtube.com/watch?v=sjnU...ature=emb_logo