Lookup Function and an external source

[NWalsh3000]

Hello, Hoping someone can help me figure this out. My Lookup function only works when the source woorkbook is open. I would like to get it to work without opening the source files.

Here is my formula when the source workbook is open

=LOOKUP(D5,'[BT Price Guide.xlsx]Mt Liq Pricing'!A:A:'[BT Price Guide.xlsx]Mt Liq Pricing'!C:C)

When the source workbook is open this formula works.

Here is my formula when the source workbook is closed

=LOOKUP(D5,'X:\bigskyf&b\ZZ BEVERAGE TRACKER TEST ZZ\[BT Price Guide.xlsx]Mt Liq Pricing'!A:A:'X:\bigskyf&b\ZZ BEVERAGE TRACKER TEST ZZ\[BT Price Guide.xlsx]Mt Liq Pricing'!C:C)

it returns #ref

how can I fix this?

Thank you!

 

[jorismoerings]

Hi,

Neither of your formula's should work.
The # REF is caused by the red colon =LOOKUP(D5,'[BT Price Guide.xlsx]Mt Liq Pricing'!A:A:'[BT Price Guide.xlsx]Mt Liq Pricing'!C:C) which is in both of your formula's.

Change the colon to a comma and it should work.

 

[Aladin Akyurek]

[...]Change the colon to a comma and it should work.

If column A is sorted in ascending order. Needs even then precaution if A is text...

 

[jorismoerings]

If column A is sorted in ascending order. Needs even then precaution if A is text...

Thanks Aladin Akyurek for the additional info. You're right.
I didn't look at the correct use of the function as the OP states

When the source workbook is open this formula works.

Which i doubt cause of the same error with the colon.
If there's no specific reason using for LOOKUP probably better to change LOOKUP into VLOOKUP or INDEX/MATCH.

 

[Aladin Akyurek]

Thanks Aladin Akyurek for the additional info. You're right.
I didn't look at the correct use of the function as the OP states


Which i doubt cause of the same error with the colon.
If there's no specific reason using for LOOKUP probably better to change LOOKUP into VLOOKUP or INDEX/MATCH.

If ascending order sort is true or possible...

=IF(LOOKUP(D5,'X:\bigskyf&b\ZZ BEVERAGE TRACKER TEST ZZ\[BT Price Guide.xlsx]Mt Liq Pricing'!A:A)=D5,
     LOOKUP(D5,'X:\bigskyf&b\ZZ BEVERAGE TRACKER TEST ZZ\[BT Price Guide.xlsx]Mt Liq Pricing'!A:A),
       'X:\bigskyf&b\ZZ BEVERAGE TRACKER TEST ZZ\[BT Price Guide.xlsx]Mt Liq Pricing'!C:C)
     "not found")

would be correct and fast.

 

[NWalsh3000]

Hello All, Thank you for your help, over the last few weeks I have tried various ways to sync my data the way I would like and ultimately have returned to the original method you were helping me with. Well it turns out that that extra colon was the culprit. Thank you Aladin for your help and your formula. It is working very well. I just have one problem i need some help with now. The cell does not auto update when I change values in the source workbook. Is there a way in VBA maybe to just make the entire workbook update upon opening? maybe a macro I could write and assign to a button that would update all formulas. THANK YOU SO MUCH FOR YOUR HELP!

Norm