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

  • Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!




    Read this to understand why we ask you to do this



    https://www.excelguru.ca/content.php?184

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!