IF, OFFSET formula referencing portion of another formula

Hi all,

I'm working on a formula that will allow me to reference a portion of another formula that references another worksheet.

What I currently have is =IF(A10>0,OFFSET((MISSING PORTION),0,7),"")

The missing portion of my formula needs to come from A10 and the formula in A10 combines two cells in another worksheet ='[FileName.xlsx]Report 1'!D6&" "&'[FileName.xlsx]Report 1'!E6

I need to add '[FileName.xlsx]Report 1'!E6 to the missing portion of my IF/OFFSET formula, but in a way that it will change based on which cell is referenced the end of the equations in Column A.

Essentially I'm trying to decrease work in the Spreadsheet so that when the formula for Column A is entered, the IF/OFFSET formulas populate automatically.

Thank you.

You would be better off if you just described in words - and included a small example - what you have, and what you want, and how it may change.

A lot depends on what the value in A10 actually looks like:

=IF(A10>0,VLOOKUP(A10,'[FileB.xlsx]Report 1'!E:L,8,False),"")

OR maybe this:

=IF(A10>0,INDEX('[FileB.xlsx]Report 1'!L:L,ROW(INDIRECT(MID(A10,FIND(" ",A10)+1,LEN(A10))))),"")


=IF(A10>0,VLOOKUP(MID(A10,FIND(" ",A10)+1,LEN(A10)),'[FileB.xlsx]Report 1'!E:L,8,False),"")