Craig

I think it's a limitation of the length of a formula that Excel allows. When
the file is closed, the formula becomes too long, because it has to include
the path of the file. This means that the formula is truncated. When the
file is open, it doesn't.

I read a good explanation of this a few days ago but I can't find it now!!

Andy.

"Craig H" wrote in message
news
Hi folks,

I've been sent 2 workbooks lets call them Week 1 and Week 2. Both
workbooks have the same structure etc with only some of the values changed.

On one of the sheets in each workbook there is a cell (P31) which contains
the formula

=IF(ISNA(VLOOKUP(C31,'C:\Agency Average\Period 73\[Period 73 Agency
Average.xls]Agency Average Bonus'!$A$1:$R$980, WEEK, FALSE)),0,
VLOOKUP(C31,'C:\Agency Average\Period 73\[Period 73 Agency
Average.xls]Agency Average Bonus'!$A$1:$R$980, WEEK, FALSE))

Now if i open 'Week 1' and tell it NOT to update links (as i don't have
the file mentioned in the formula above) then the cell P31 displays £35. If
i close all this down and then open 'Week 2' (again not updating links) then
cell P31 in this workbook returns £190. At this stage everything is fine and
the values are correct.

However, if i leave 'Week 1' open and then open 'Week 2' the cell P31
returns £190 in BOTH workbooks !!!! If i have the 'Week 2' open showing £190
and then open 'Week 1' which has £35 then BOTH show £35 !!!!!!

Does anyone know what is happening ? Does Excel store this lookup value
somewhere which then gets mixed up ?

Any help would be greatly appreciated.


TIA

Craig.