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.  🙂

6 thoughts on “Using Named Range to Refer to Cell Above

  1. Isn't the drawback eliminated by defining the scope of a name to the worksheet only? You showed how to do this in 2007. In 2003, include the sheet name in the Name:

    Sheet1!CellAbove

    Or use Jan Karel Pieterse's Name Manager (http://jkp-ads.com).

  2. Hi Jon,

    Should have been clearer... The CellAbove reference, scoped to a single sheet in 2007 means you can use the same name throughout the workbook. You just have to set it up for each sheet which is kind of a pain.

    In 2003, you only get one shot at using the name (through the UI anyway.) Yes, you could set up each name prefaced by the Sheet name, but I think it's kind of ugly. I'd prefer to set up a name once and be done with it.

  3. In 2003 it's the same number of steps as in 2007 and 2007 is only moderately easier (prefix with sheet name vs., selecting sheet name from dropdown). If you forget, select the Name, prefix the sheet name in the Name box, click Add, then activate another worksheet, select the name (which is not associated with a single sheet), and delete it.

    Or use JKP's Name Manager (honest, he pays me nothing for this endorsement).

  4. Ken,

    Jon has mentioned it two times and we all know that any advertisement should be at least mentioned three times so:

    Use JKP's Name Manager which is still free (which I actually find surprising to be surprising consider all the time and efforts that has been put into it).

    Kind regards,
    Dennis

  5. Dennis -

    Thanks for the independent endorsement!

    "Use JKP’s Name Manager which is still free (which I actually find surprising to be surprising consider all the time and efforts that has been put into it)."

    It's a labor of love.

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *