VLOOKUP question

[Guest]

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 formul

=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.

TI

Craig

 

[Andy B]

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.

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 ?