Alternative to Offset in a formula

[ashokunbi]

Hello, I found out that certain functions do not work with closed source files and one of them is offset which I am using in my formula: =OFFSET('[Master CPFR.xlsx]MOT VZN'!$B$1,MATCH($D$2,'[Master CPFR.xlsx]MOT VZN'!$B$1:$B$300,0),34). Please look at the formula and suggest what would work with a closed source file getting the same values and keeping the values.

Thanks
Ade

[martindwilson]

looks like you want the value from '[Master CPFR.xlsx]MOT VZN'!$A$j2:aj300
=INDEX('Master CPFR.xlsx] vzn'!$AJ$2:$AJ$300,MATCH($D$2,'Master CPFR.xlsx] vzn'!$B$1:$B$300,0))

[ashokunbi]

Thanks martindwilson. That worked perfectly. I appreciate the help.
Ade

[ashokunbi]

martindwilson, I just realized that the tab information turns #Ref after I reopen the file:

INDEX('U:\Sourcing\Vendor Demand Planning\Master CPFR\[Master CPFR.xlsx]#REF'!$Y$2:$Y$300,MATCH($D$2,'U:\Sourcing\Vendor Demand Planning\Master CPFR\[Master CPFR.xlsx]#REF'!$B$1:$B$300,0))

Is there a fix for this?

[martindwilson]

cant see why but then again i cant recreate that file path