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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,454
Messages
6,136,734
Members
450,025
Latest member
Beginner52

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
Back
Top