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!

 

[Gerald Higgins]

Hi, welcome to the board.

There may well be better ways of doing this, but one way might be to look at using the OFFSET function on your totals sheet.
For example

=OFFSET('DATA'!A1,1,0,1,1)

will return the value from cell A2 on sheet called DATA, even if you repeatedly delete row 2 on that sheet.
Won't work if you delete row 1, but as long as you are careful not to, then that may not be a problem.

Modify it to this

=OFFSET('DATA'!A$1,ROW(),0,1,1)

and you can copy it all the way down the column.

Check that this does what you want, before relying on it too much !

 

[doubledeuce]

Hi Will,
Thanks so much for the quick response, but either I'm misunderstanding you, or using the OFFSET function doesn't really help in my situation. You mentioned there might be a better way of doing this, and I think you're probably right. I'm very open to any suggestions of a better way. I'm not committed to the lists I currently have.

Thanks so much for any other suggestions. If some part of my description was unclear and that's why you thought the OFFSET function would help, please let me know and I'll go into more detail and clarify what I need.

Thanks again!

 

[Gerald Higgins]

Well can you explain why OFFSET doesn't do what you want ?

 

[doubledeuce]

Wow, either I completely misread your first post or maybe you edited it slightly, because what's there now is definitely a lot closer to a solution I think. Unfortunately I'm still a little confuse about the ROW() part of your formula. I'm not really sure how that works. I'm able to get it to reference the appropriate sheet, but it's referencing row 15 instead of the row I want.

Can you please explain what ROW() means and how to use it?

Thanks so much.

 

[Gerald Higgins]

Yes I did edit it at the time, I accidentally posted before I'd finished typing. I was wondering why you were calling me Will

The ROW() function asks Excel to return the row number for a specified address.
For example, putting this in A1

=row()

will return 1.
Try putting it in A2, and see what happens.

You can also do this

=row(a1)

Try replacing ROW() in my original formula, with ROW()+15, or something similar.

 

[doubledeuce]

Oh man, this is SO CLOSE! I have what I expect to be the last problem.
My totals sheet is called Totals, then two of my data sheets are called Contacts and Email.

So I wrote the formula

=OFFSET(Contacts!A$8,ROW(A1),0,1,1)

This worked great as the actual data starts on A9, so selecting A$8 means that I'll never accidentally delete the first row, and using your ROW(A1) function seems to be a great idea too because as I drag the formula down, ROW(A1) increases relatively. PERFECT... so far.

The problem is that I did the same thing referencing my email sheet below the Contacts sheet

=OFFSET(Email!A$8,ROW(A1),0,1,1)

Then once I had a list of both, like I said in my first post, I want to have them mixed up showing an aggregate list ordered descending. So when I change the ordering of the list for some reason some of the email sheet formulas turn into #REF! errors with the formula

=OFFSET(Email!A$8,ROW(#REF!),0,1,1)

Any idea why changing the ordering is causing that problem and how it can be avoided.

Thanks again for your help so far.

 

[Gerald Higgins]

Sorry, I don't understand what you mean when you say "mixed up showing an aggregate list".

Can you explain please ?

Are you saying that, for example, on your Totals sheet, one cell may contain a reference to the Contacts sheet, and the cell below it may contain a reference to the Email sheet, and the cells below contain references to either sheet, more or less at random ?

 

[doubledeuce]

Yeah, I didn't do the best job of explaining it. It is kind of confusing though. I've got my Contact sheet which is basically a list of data and values for each piece of data. Lets say one of the rows on the contact list has data of CONT1 and CONT1 has a value of 32. Then on my Email sheet (which is formatted the same way) I have a row of data EMA1 with a value of 15.

Lets also assume my Contact sheet currently has 5 rows in it and my Email sheet has 7 rows in it. Well I'll be adding to both those lists regularly, so I'm going to take 10 rows from each (to allow room for me to add to both lists) and reference them on my Totals sheet using the formula we've been talking about.

Once I've done that my totals sheet will look something like this:

CONT4  53
CONT3  46
CONT5  44
CONT1  32
CONT2  12
EMPTY ROW
EMPTY ROW
EMPTY ROW
EMPTY ROW
EMPTY ROW
EMA3  78
EMA5  65
EMA1  15
EMA4  13
EMA7  10
EMA6  8
EMA2  1
EMPTY ROW
EMPTY ROW
EMPTY ROW

Now here's the part I'm having problems with. I want to take this list and sort it by the Values column regardless of which sheet is referenced so my totals sheet looks something like this:

EMA3   78
EMA5   65
CONT4  53
CONT3  46
CONT5  44
CONT1  32
EMA1   15
EMA4   13
CONT2  12
EMA7   10
EMA6   8
EMA2   1
EMPTY ROW
EMPTY ROW
etc.

Hopefully that clears it up. Sorry if I went overboard with my explanation, but sometimes just showing an example is the easiest way to explain it.

Thanks again.

 

[Gerald Higgins]

Yeah OK I think I understand. Sorry I missed that in your first post.
I'll need to think about that.