Referencing cells from excel workbook to another showing #Ref when master workbook is closed

[BayanMobarak]

Hello Guys,

My problem is pretty simple, I have a values that will be updated, so I`m referencing those values in another work book. What I`m doing is writing =CellName and it worked fine when both workbook are open, but when closed I find the error of #Ref.

Hera are the files I`m trying to have the value of this period column p6 test workbook to be referenced in Last Period column ref should be here workbook

The master workbook and the excel workbook where the values should be referenced are both attached. 

Your help is very appreciated 

[#2 · Accepted solution]

@BayanMobarak 

It's because of the structured table references. See Links to data tables residing in external files display #REF! 

This will work:

=IF('[p6 test without dalas connection.xlsx]Sheet1'!C2="",2,'[p6 test without dalas connection.xlsx]Sheet1'!C2)

When you close the other workbook, the formula will still work.

[#3]

It worked perfectly , thank you so much!

but can please clarify what changes do you made to make it work?
I can see that we are not referring to the table but to the sheet. What changes should I make next time when I`m working with tables ?

Thanks again bro @Hans Vogelaar

[#4]

@BayanMobarak 

As the article I linked to in my previous reply states, structured table references to another workbook only work if that workbook is open. So you cannot refer to the table name and column name in the formula. Instead, you have to refer to the worksheet name and the cell reference.