Cell referencing help

[tsukami]

A'righty, I will attach my workbook. This is for my midterm project.

In worksheet 'budget overview' I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.

I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.

Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?

I hope that made sense!

ps: I'm well aware of spelling errors, I will fix them all when the workbook is finished.


Budget Project Again.xlsx

[daffodil11]

Ash,

It's changing because you're using the SUBTOTAL function instead of SUM.

SUBTOTAL ranges only exist when they aren't hidden. So when you filter Housing and the other numbers go all ninja-like, it's like they don't even exist.

You can instead use SUM(E9:E45) and it will still add up the numbers even if they are hidden.

I'd just can change Overview I12 to =SUM(E9:E45) and keep the subtotal in place so that things look right.

I13: =SUM(f9:f45)

I14: =I12-I13

This way your Overview remains a total of all expenses, and your Subtotal on Expenses is just the sum of what's visible on that page.