Problems with source file update with OFFSET Formula

[cogumelo]

Hi.

I'm using the following formula in FILE Z

=OFFSET([WRT_51_PD_master.xls]pivot_pd!$A$4;MATCH($A2;[WRT_51_PD_master.xls]pivot_pd!$A$5:$A$150;0);MATCH(CONCATENATE("GT ";$F2);[WRT_51_PD_master.xls]pivot_pd!$B$4:$BO$4;0))

This formula is getting it's values from a pivot table in FILE X.

Whenever i open FILE Z and FILE X the updating works fine. However if i do open FILE Z alone and i choose not to update this formula doesn't show the info stored on the file and turns into the error #VALUE!

Is this a problem with this formula?

I'm using some other formulas and they all work fine if i choose not to update the old values stored are kept.

My purpose is to send this file periodically to people therefore i'm trying to have it in a way that i can update just by opening both files and that other people can see the values stored.

Is there a way to work around this formula in order to allow it to show the values stored if i decide not to update?


thx in advance,

cogumelo

 

[cogumelo]

can anyone help me out?

thx in advance,

cogumelo

 

[Domenic]

Try...

=INDEX([WRT_51_PD_master.xls]pivot_pd!$B$5:$BO$150;MATCH($A2;[WRT_51_PD_master.xls]pivot_pd!$A$5:$A$150;0);MATCH(CONCATENATE("GT ";$F2);[WRT_51_PD_master.xls]pivot_pd!$B$4:$BO$4;0))

Hope this helps!

 

[cogumelo]

thanks domenic.

worked great