Cells wont update unlees other workbook open

Wookie

Board Regular
Joined
Mar 4, 2003
Messages
220
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?
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:
Care to post up your formula so we can have a look at it?

plettieri
 

Wookie

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

Ragnar78

Board Regular
Joined
Feb 10, 2004
Messages
210
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
Joined
Mar 4, 2003
Messages
220

ADVERTISEMENT

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

Ragnar78

Board Regular
Joined
Feb 10, 2004
Messages
210
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
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Mar 4, 2003
Messages
220
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,563
Messages
5,625,528
Members
416,116
Latest member
Joemamasuka

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top