Posts by ShoYnn

  • Offset Cell("address")

    [xpost][/xpost]


    mrexcel.com/board/threads/offsetting-cell-address.1196242/



    I have a table. The first column has job names. The second column uses cell("address") to give me the sheet and cell address that the job is found. That all works great. What I would like to do though is add a few rows to the results of the cell("address"), for example instead of the result being sheet2!$D$1 I would like it to return with sheet2!$D$4. Anyone got anything for this?


    Here is the original formula I am working with


    =Cell("address",Index(Indirect("'"&$O$1&"'!$D$1:$FM$1"),Match(AM3,Indirect("'"&$O$1&"'!$D$1:$FM$1"),0)))


    O1 is a cell that contains the sheet name to look in, D1:FM1 are the cells that will contain the value being matched, and AM3 contains the value to match. The returned response is


    ' FY22 Teams'!$D$1


    Which is accurate, but I would like it to return the cell a few rows down


    When I try to do a +3 on the index part it adds 3 to the column, but the row stays at 1. Example formula is


    =Cell("address",Index(Indirect("'"&$O$1&"'!$D$1:$FM$1"),Match(AM3,Indirect("'"&$O$1&"'!$D$1:$FM$1"),0+3)))


    Response is now


    'FY22 Teams'!$G$1


    Where I want to add the 3 to the rows and keep the column the same