Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Have a BEFORE() function that refers to the cell above, below, left or right of current cell

If I want cell C5 to refer to the cell above and add 1, then I would usually use: =C4+1. But this has the problem that if I insert a row at row 5, then C6 (where the formula has been shifted down to) now refers to two cells above plus 1.

I can get around this by using: =OFFSET(C5,-1,0)+1, but this feels like overkill and I am now using a volatile function.

It would be useful to have a function that refers to one cell above, left, right or below the current cell, e.g. = BEFORE(1)+1, where:

1 = above, 2 = left, 3 = right, 4 = below.

3 votes
Sign in
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

Gary shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →


Sign in
Sign in with: facebook google
Signed in as (Sign out)
  • Jon Wittwer commented  ·   ·  Flag as inappropriate

    This is a good suggestion. A running balance that doesn't break when you delete rows is a very common use case. The method I use when I want to avoid using the volatile OFFSET function is to create a "relative named range" such as cell_above or cell_left or prev_balance.

    1. Select cell C5
    2. Go to Formulas > Define Name
    3. In the Name field, enter "cell_above"
    4. Set the Scope to the current Worksheet
    5. Set the Refers field to =C4 (no dollar signs)
    6. Use cell_above in a formula such as =cell_above+1

  • Uncle Bob commented  ·   ·  Flag as inappropriate

    Sub DefAbove()
    ' My checkbook.xlss spreadsheet has a column named 'Balance'.
    ' Old formula was ugly: =index(rows()-1,columns()) + credits - debits
    ' New formula is clean: =Above+credits-Debits
    ' Also if [a1].formula has '=sum(a1:above)' it will evaluate as =sum(a1:a9)
    ' note: This was tested with Selection at all 4 corners of Xls and Xlsx workbooks.
    ' If your formula is in row 1 or in column A, the RefersTo will be the last Row or Column (Row 1048576/XFD depending on release).

    On Error Resume Next
    ActiveWorkbook.Names.Add name:="Above", RefersToR1C1:="=!R[-1]C"
    ActiveWorkbook.Names.Add name:="Below", RefersToR1C1:="=!R[1]C"
    ActiveWorkbook.Names.Add name:="Before", RefersToR1C1:="=!RC[-1]"
    ActiveWorkbook.Names.Add name:="After", RefersToR1C1:="=!RC[1]"
    ' this is a safety net in case the workbook previously had a different definition of Above or Below or Before or After.
    Above1: If InStr(1, "=!R[-1]C" & Replace("=!R[-1]C", -1, Application.Rows.count - 1), ActiveWorkbook.Names("Above").RefersToR1C1, 1) = 0 Then Stop: MsgBox "'Above' was not assigned"
    Before1: If InStr(1, "=!RC[-1]" & Replace("=!RC[-1]", -1, Application.Columns.count - 1), ActiveWorkbook.Names("Before").RefersToR1C1, 1) = 0 Then Stop: MsgBox "'Before' was not assigned"
    Below1: If InStr(1, "=!R[1]C" & Replace("=!R[1]C", 1, 1 - Application.Rows.count), ActiveWorkbook.Names("Below").RefersToR1C1, 1) = 0 Then Stop: MsgBox "'Below' was not assigned"
    After1: If InStr(1, "=!RC[1]" & Replace("=!RC[1]", 1, 1 - Application.Columns.count), ActiveWorkbook.Names("After").RefersToR1C1, 1) = 0 Then Stop: MsgBox "'After' was not assigned"

    On Error GoTo 0
    End Sub

  • Uncle Bob commented  ·   ·  Flag as inappropriate

    This comes close.
    Select cell a2
    alt M M D
    name:= Before refersto: "=!a1"

    select a2
    alt M M D
    name:= after refersto: "=!a3"

  • Kenneth Barber commented  ·   ·  Flag as inappropriate

    If such a function is made, it should be called BESIDE or ADJACENT.

    As you mentioned, OFFSET already solves your problem, but it is volatile. I think that it's better to solve the issues related to volatile functions than to work around them with new functions. See the link below.

    Some people would argue that BEFORE should be extended to an arbitrary number of cells away from the current one. For example, if I have 2-column groups labelled diesel, gasoline, and propane, and within each group I have a volume column and a cost column, then if I want to sum the costs, I need to sum every 2 cells. If I add a new fuel, I need to insert 2 columns at once.

    Lastly, your summation example can avoid BEFORE and OFFSET altogether. If you want a cumulative sum of A1:A10, use SUM(A$1:A1). Then when you drag the formula down, A$1 will stay the same but A1 will change to A2, A3, etc.

    If you are using a table, use a formula like SUM(TableName[[#Headers],[Column Name]]:[@[Column Name]]).

    If you feel that we still need a BEFORE function, feel free to counter my argument.

Feedback and Knowledge Base