Inserting a formula as a cell reference to another formula

2
votes

I am trying to build a formula which will combine OFFSET (because I want to retrieve every other row from one worksheet onto another) and Match()), because I want the function to find the first cell reference using a lookup value. Independently, my index/match works, my Substitute(Address()) works (to retrieve the column#row# instead of the cell value) and my Offset works. However, when I put the substitute(Address()) inside of my Offset function (as the cell reference), the formula stops working.

-Match Function (works, finds the column number)

MATCH(T2, 'Sheet1'!1:1, 0)

-Substitute (works, switches column alphanumeric for column number)

SUBSTITUTE(ADDRESS(1,MATCH(T2, 'Sheet1'!1:1, 0), 4), "1", "")

-Offset alone (works, retrieves every other row from sheet 1)

OFFSET('Sheet1'!$O$2,(ROW(D3)*2)-6,0)

-Final formula (doesn't work)

OFFSET('Sheet1'!$SUBSTITUTE(ADDRESS(1,MATCH(T2, 'Sheet1'!1:1, 0), 4), "1", "")$2,(ROW(D3)*2)-6,0)

I feel like it must be a problem with inserting the other formulas as a cell reference for OFFSET, but I can't figure out how to fix it.

2
excelexcel-formula

2 Answers

2
votes

You need to add INDIRECT:

OFFSET(INDIRECT("'Sheet1'!$"&SUBSTITUTE(ADDRESS(1,MATCH(T2, 'Sheet1'!1:1, 0), 4), "1", "")&"$2"),(ROW(D3)*2)-6,0)
0
votes

The problem is the way you try to build your address. You can't just build a text that looks like an address. I think that SUBSTITUTE function is unnecessary. But you need INDIRECT. It converts a text into a real address. I believe this is what you need at the end:

OFFSET(INDIRECT(ADDRESS(2,MATCH(T2, 'Sheet1'!1:1, 0), 4, 1, "Sheet1")),(ROW(D3)*2)-6,0)