Cells wont update unlees other workbook open

[Wookie]

I have a sheet where the formulas relate to cells in other workbooks. i get N/A error unless the other workbook is open. Any ideas?

 

[plettieri]

Hi:
Care to post up your formula so we can have a look at it?

plettieri

 

[Wookie]

=HLOOKUP(Month,'H:\Submitted Packs\[CoA Ltd.xls]JVB'!$D$4:$P$57,ROW(C5)-1,FALSE)

 

[Ragnar78]

In fact the formulas are calling values that are found in another workbook
this is why you get the N/A
=HLOOKUP(Month,'H:\Submitted Packs\[CoA Ltd.xls]JVB'!$D$4:$P$57,ROW(C5)-1,FALSE)

so in fact...if u have the JVB sheet in the worbook your working on, you can delete from the formulas all the red marked sentence where the data should be looke for the HLOOKUP

 

[Wookie]

I has to reference the external workbook. Why would this cause the error?

 

[Ragnar78]

if you have to keep the references from another workbook than the other workbook should be open.

It creates an Error cause Excel treats only the opened Excel files

 

[just_jon]

Well -- like the car rental commercial says -- not exactly! Under Tools | Options | Calculation tab -- do you have a check mark by Update remote references?

 

[Wookie]

Indeed it is checked

 

[just_jon]

Sorry, not paying attention. I think the problem is that your variable Month contains a value lesser than the lowest value contained in the referenced array -- can you check the value of Month as it is now, and see if it exists in the bounds of the array?

 

[Wookie]

Month equal Jan, This in cell E4 in the JVB sheet. The formula works when i change Month to Feb, Feb is shown in F4 in JVB sheet.