Silly Question: formulas referencing tables in closed workbooks

[spydey]

So this might be a silly question .... I have done some research and haven't quite found an answer that either makes sense to me, or is clear enough.

I am having an issue with some of my formulas.

I have workbook 1 (wb1) and workbook 2 (wb2).

In wb1, I have some formulas, let's use Vlookup as an example.

In wb2 I have some data in a table, created via ctrl+T.

My wb1 formula references the data in the wb2 table.

When both workbooks are open, works great.

However, if I open wb1 first, then my formulas return REF# errors.

Once I open wb2, then it auto-updates and all is good.

If I close wb2, the results stay until I update something in wb1 then I get the REF# errors again.

I realized that if I convert the wb2 table back to a range, then even if I don't have wb2 open, the formulas in wb1 work fine.

Why is that? Why can't a formula in wb1 referencing a table in wb2, update correctly if wb2 was not opened prior to wb1 being opened and/or is closed aftwards?

The issue appears to be related to the table objects in outside workbooks, at least that is my observation/conclusion. Am I right in my assumption/observation?

Thanks for taking the time to read my silly question .....

-Spydey

 

[Scott T]

Some formulas can not handle closed workbooks, like SUMIF. Vlookup should work on a closed workbook. I do not get the REF error unless I go to the cell with the Vlookup and go into edit mode and then hit enter. When I do a vlookup with cell references instead of table formula nomenclature It does give the REF error so excel looks to have a problem with tables in closed workbook.

 

[spydey]

Some formulas can not handle closed workbooks, like SUMIF. Vlookup should work on a closed workbook. I do not get the REF error unless I go to the cell with the Vlookup and go into edit mode and then hit enter. When I do a vlookup with cell references instead of table formula nomenclature It does give the REF error so excel looks to have a problem with tables in closed workbook.

YEah, that is what I was getting. Using Cell reference in the formula works fine with a closed wb, but using a table reference with a closed wb is giving me the REF# error.

-Spydey

 

[Scott T]

You may find this MrExcel video helpful as to what is happing. Apparently when the vlookup points to a table then the data is not saved in the file with the vlookup like it is when it uses cell references.

https://youtu.be/-0cDSt0Io40

 

[spydey]

Hmmm, interesting ..... thanks for the info @Scott T !!

-Spydey