[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