Using Named Range to Refer to Cell Above

The last couple of times I've taught, I've received a question about how to deal with the following scenario:

• You have a table of data, say in A1:A10
• In A11, you have the following formula:  =sum(A1:A10)
• Someone highlights row A11, right clicks it, and choose Insert Row

The effect of this, of course, is that cell A11 moves to A12, but the formula remains the same.  The user puts a new value into A11, and it isn't added in to the total.

This can be pretty frustrating, especially if the user who inserted the row isn't astute enough to check the formula.  One of my student expressed a great deal of frustration that she'd tried everything, even hiding the row above, but it made no difference.  (I'm not sure what else "everything" consisted of, to be honest.)

Of course, the reason hiding the row above didn't work is that Excel inserts a row above the selected row.  So the while the hidden row is above the totals row, Excel still inserts a new row just above the (selected) totals row, but below the (hidden) row above that.

At any rate, there is a trick that we can use to fix this problem: A defined name to refer to the cell above.  Here's how:

• Open a new workbook
• On Sheet1, select cell A2
• Open the Name dialog:
• In Excel 97-2003, you'd go to Insert-->Name-->Define
• In Excel 2007, go to the Formulas Tab-->Define Name
• Enter CellAbove as the Name
• Enter =Sheet1!A1 in the RefersTo box
• In Excel 2007 (Only), change the scope to Sheet1 from Workbook

Click OK when done to return to the worksheet.

The important points to note about this:

• You were in cell A2 when you opened the dialog
• You created a named range that pointed to cell A1 (not A2)
• You stripped the $ signs from the range, making it relative, not absolute

So now, if you enter =CellAbove in your cell, it will point to the cell above.  (Unless you enter it in A1, at which point it will point to the very last row of your worksheet.)  Try it out.  Enter a value in the a cell, then go to the cell below and enter =CellAbove.  It should come back with that value.

The nice thing about named ranges is that you can use them in formulas.  So now, let's look back at our original issue.  We're looking at A11 containing the formula =Sum(A1:A10).  What would happen if we changed it to this:  =Sum(A1:CellAbove)

Curious?  Try it out!  Once you've got the formula set up, highlight row 11 and insert a new row (or ten rows, it won't matter.)  Enter some numerical data in the new row(s), and watch it add up.  🙂

The drawback of this is that you can only use the CellAbove reference on one worksheet, as it refers to that specific sheet when you set up the name.  (And using the same name on another sheet will overwrite it.)

There is a way to do it, but that will be the subject for another blog post.  🙂