Reference a Closed Excel Workbook

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
First off, I am going to try to avoid using VBA due to other circumstances.

This is how I am operating at the moment:
I have 2 workbooks referencing cells off each other, my active workbook that I will be always using (let's call this Book 1) and my passive workbook (Book 2) that I would like to keep separate from the other. I have both workbooks open on my screen. I input a value into my input cell in Book 1, and then Book 2 pulls this cell value. Book 2 sorts through its sheets and tables to determine what values need to be outputted to its output cells. I then have Book 1 pulling values from Book 2's output cells. So it goes [input into Book 1] --> [Book 2 reads this input] --> [Book 2 processes data] --> [Book 2 outputs] --> [Book 1 reads output from Book 2].

What I want to happen is I want to have Book 2 closed. So when I enter in my input value into Book 1, I do not pull any output values from the close Book 2. Is it possible to fix this without VBA?

Thank you for the help,
Nick
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
hi, Nick

If you're set up like a database, that sort of thing might be possible - in a limited way. (Depends, I think, on what processing needs be done in Book 2.) Without VBA it will be a bit more work - to manually execute the queries.

Such as from Book 1 run an UPDATE query to have the Book 1 input loaded into the closed Book 2. Without VBA this might mean opening MS Query or however you run queries in your Excel version. Then if the result back from Book 2 can be obtained by querying Book 2, then run that query from Book 1. Again, this would only work if Book 2 is basically data tables, not (whilst it remains a closed file) formulas. If you needed Book 2 to calculate formulas then the file would need to be opened.

With this specific set up - no formulas needing to be calculated in Book 2 - then instead of Book 2 being an Excel file it could be an mdb file, or other storage file/s.

For easier operation, or to have Book 2 calculate formulas or run code, then Book 2 would need to be opened. VBA would certainly make that easier to use.

HTH. regards, Fazza
 
Upvote 0
Fazza,

Thank you for the reply! I see exactly what you're saying, and I think you solved my problem.
Book 2 is essentially multi-sheet data tables (the actual use of Book 2 is a list of tool numbers used for a certain part - multiple tools are used for each single part and the tools are sorted by what task they perform).
I have a single sheet in Book 2 that "searches" through every data table for all the tool numbers associated with a given part number. I am using formulas in this. I could just move this sheet from Book 2 to Book 1 - keeping Book 2 a static workbook without any formulas.

Thanks again!
Nick
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

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