CODEWITHSUNDEEP
excelexcel-formula
Microscone
Microscone

Reputation: 184

Inserting a formula as a cell reference to another formula

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.

Upvotes: 2

Views: 2110

Answers (2)

Rory
Rory

Reputation: 34045

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)

Upvotes: 2

Egan Wolf
Egan Wolf

Reputation: 3573

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)

Upvotes: 0

Related Questions