0

In my sheet I have a few cells containing different cell references with the same row index.

Now I want to reference these few cells but add a fixed row offset to it:

Screenshot

So the desired result to have in these cells is the information that is in cell "AG11", "AH11",....

I could easily manually copy the range and change the row number by hand, but I was curious, if there is a function/command that achieves that.

Can sth like this be done with =AG&"6+5" or similar syntax?

Thank you.

2
  • Are you aware of the function ROW()? Without parameter, it gives the row number of the cell where you type the formula. With parameter, it gives the row number of the reference you've given. E.g. select cell "C3" and type =ROW(), you'll get 3. Type =ROW(A1) you'll get 1.
    – Dominique
    Mar 24, 2023 at 9:03
  • Yes, I know this function. The problem was I thought too complicated. I just discovered that for my purpose I could have just used the "Replace" Function.
    – Creatronik
    Mar 27, 2023 at 8:25

1 Answer 1

0

The direct answer to:

Can sth like this be done with =AG&"6+5" or similar syntax?

would be:

=Indirect("ag"&6+5)

this one wont update if you delete or add rows or columns

A column shows formula on B column Example 1


you could also use:

=Indirect(Address(Row(AG6)+5,Col(AG6)))

or

=INDIRECT(ADDRESS(6+5,33))

this last one wont update if you delete or add rows or columns

Example 2 this examples above uses Google Sheets but should apply to Excel


Although if what you want is a one time change of a large amount of cells that would be possible via macro or a custom external program that would read the workbook and change the appropriate cell references, both should return prettier solutions if your end goal would be to just have =AG11.

As an example of how this would work, in OpenOffice:

Sub Main
    Dim sheet As Object
    sheet = ThisComponent.CurrentController.ActiveSheet
    For i = 1 To 5
        Dim formula As String
        Dim address As New com.sun.star.table.CellAddress
        Dim cellName As String
        formula = sheet.getCellRangeByName("B" & i).Formula
        address = sheet.getCellRangeByName(Right(formula, Len(formula)-1)).getCellAddress()
        cellName = Chr(address.Column + 65) & (address.Row+2)
        sheet.getCellRangeByName("B" & i).Formula = "=" & cellName
        Next i
End Sub

Would change: A column shows formula on B column before macro

to:

after macro

I can't share example of the macro in Excel because I dont own it, I could do an external program example but it might be out of scope for this question.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .