Excel - Reference other sheets without them being open?

Question

Suppose I have Sheet A and Sheet B, where Sheet A has a bunch of cells which refer to cells in Sheet B. Usually these cells will update fine when Sheet B is open. However, if I close both sheets and reopen (and update) Sheet A, it gives me errors.

How do I avoid these errors?

I can't just click "don't update" since I sometimes make modifications to sheet B with sheet A closed.

Also, in my particular case I really have a sheet A that refers to sheets B through Z. I'd rather not have to open all of these sheets at once.

Answer 1

Normal links should update automatically (or with prompting) even when the other workbooks are closed.

However, if you have links using the INDIRECT() function, then the other workbooks must be opened.  This formula is an exception to the general rule.

Answer 2

what kind of errors are you getting?  Normally when you have one sheet linked to another and you open up the first one, excel will prompt you to either update or don't update the links.  If you choose "update" excel goes off to the other file (unopened one) and looks for any changes in the values since the last time it updated.  Then all your formulas in the first sheet are updated and recalculated using the current values from both sheets.  If you choose "don't update" your first sheet may not have the most up-to-date figures.  But either way you shouldn't be getting any errors.

so...you'll have to be more specific on what errors you're getting.  Are they different if you choose "update" instead of "don't update"?