Deleting Referenced Cells causes #REF!

[doubledeuce]

This is my first post, so I apologize if this has already been answered, but I've searched high and low without finding a solution.

I have a number of worksheets with separate lists in them. Each list essentially has items in one column and values for those items in an adjacent column.

The problem is that I also want a "Totals" worksheet. This worksheet will also be a list that needs to do 3 things:
1. Pull in the values from all the other lists
2. Aggregate and reorder them so that I get one master list sorted by the totals.
3. *This one's the problem* Set it up in such a way that if I ever delete a row from any of the referenced lists/worksheets, that item will also be removed from the "Totals" worksheet and won't leave behind a #REF! cell.

Of course in my 3. requirement I also need the list to remain in order as items are deleted from the referenced lists. So say for example I have ItemX in one of my lists and it has the highest value of any item in any list. Well in my Totals Worksheet/list ItemX would appear at the top of the list. Now say that I delete ItemX from the referenced worksheet/list, I want it to be deleted from the Totals list and for the item with the 2nd highest value to assume the top spot.

I've tried referencing the lists with absolute and relative references, but in both cases, if a row is deleted, it messes things up in the Totals list leaving behind #REF!.

Please help.

I'm using Excel 2008 for Mac.

Thanks!

 

[doubledeuce]

No problem. At least we're both on the same page now!

Thanks again. It doesn't seem like a complicated problem, but it's been plaguing me for a long time. If you can think of a completely different way of doing the same thing, that doesn't involve lists or whatever feel free to suggest it. I'm not stubborn.

Thanks