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
(thinking…)
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 →

6 comments

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • 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.
    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10769886-today-volatility-reduction

    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