# Cells wont update unlees other workbook open

#### Wookie

##### Board Regular
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?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### plettieri

##### Well-known Member
Hi:
Care to post up your formula so we can have a look at it?

plettieri

#### Wookie

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

#### Ragnar78

##### Board Regular
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

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

#### Ragnar78

##### Board Regular
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

##### Legend
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

##### Board Regular
Indeed it is checked

#### just_jon

##### Legend
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

##### Board Regular
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.

Replies
2
Views
99
Replies
3
Views
117
Replies
1
Views
745
Replies
0
Views
856
Replies
1
Views
556

1,171,044
Messages
5,873,449
Members
432,980
Latest member
KMorrison12345

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back