Expenses Spreadsheet

[allylamb]

Hi there!

I'm trying to create a workbook that will allow me to enter expense information (receipts) on one worksheet (The columns are the following: Date, Payee, Category, Subcategory, Description, Subtotal, GST, PST, Total), and then based on the subcategory entered for that particular receipt I would like to have the numerical value in the corresponding "Total" cell link to a cell in another worksheet.

My hope is that this linked cell can then add up any numerical information that it receives from multiple cells on the first worksheet (based on identifying the Subcategory name).

Ie. 

If I enter the word "Facebook Ads" into the subcategory cell for that particular row (receipt) then I would like the numerical value in the corresponding "Total" cell to be linked/sent to a cell on my second worksheet. I'm hoping that one cell can total up the numerical values it receives from any cells identified as fitting the "Facebook Ads" subcategory.

I hope my explanation/description of my idea makes sense!

I'm not sure if this idea is possible, but I would love any advice or suggestions! I know basic formulas in Excel, but need help with this more complicated project. 

Thanks!!

[#2]

@allylamb 

You seem to have thought out a good data structure for your project. I suggest you familiarise yourself with Pivot Tables. I believe it's very suitable for the kind of analysis you want. The attached workbook gives a very simple example. Hopefully, it helps you to get started.

NOTE: I put the table directly under the data, so that you have all in one view. You usually put the table in a separat (new) sheet though.

[#3]

@allylamb 

Riny's suggestion--Pivot Table--is exactly on target. The Pivot Table is one of the most useful ways to summarize exactly the data you've got. If it weren't so personal I'd post my own expense tracking workbook. Thousands of transactions (over the course of the year, checks, bills paid, credit cards) get summarized on one page, by month, by category and sub-category. Exactly what you're trying to do.

I have attached yet another "simple" example, however. And want to draw your attention to another feature that Riny didn't mention. If you click on any cell in the Pivot Table, it will drill down to the underlying rows in the database, which is VERY useful.

Have fun!

[#4]

Thank you so much!

I will have to read up and familiarize myself with this concept!

Much appreciated!

-Ally

[#5]

Thank you as well! It is reassuring to see multiple people suggesting the same concept!

The examples are also super helpful!

-Ally