#ref! error in vlookup between two workbooks

[acramer]

I have a vloopup calculation between two workbooks. The vlookup returns #ref! errors when the second workbook isn't open.

You can get the references to work by opening the main workbook, then opening the sub workbook, clicking save. Next time you open it you have to click don't update links otherwise the references will break again.

Any suggestions on how to either not prompt for the "update links" or keep the vlookup in tact if the users does click update links?

 

[Aladin Akyurek]

I have a vloopup calculation between two workbooks. The vlookup returns #ref! errors when the second workbook isn't open.

You can get the references to work by opening the main workbook, then opening the sub workbook, clicking save. Next time you open it you have to click don't update links otherwise the references will break again.

Any suggestions on how to either not prompt for the "update links" or keep the vlookup in tact if the users does click update links?

Imvoking a VLOOKUP formula involving a closed book should not be a problem. Are you perhaps using an INDIRECT call in the formula?

 

[acramer]

Imvoking a VLOOKUP formula involving a closed book should not be a problem. Are you perhaps using an INDIRECT call in the formula?

We aren't using INDIRECT, we are however using CONCATENATE in the VLOOKUP does that act the same as the INDIRECT does?

 

[Aladin Akyurek]

We aren't using INDIRECT, we are however using CONCATENATE in the VLOOKUP does that act the same as the INDIRECT does?

Care to post the formula as you have it?

 

[acramer]

=VLOOKUP(CONCATENATE($A$3,$A16),'C:\Users\acramer.DOMINIUM\Desktop\New folder\`2013 Budget Data Input Sheet.xlsx'!Table3[[Lookup Value]:[Annualized Total]],2,FALSE)

 

[RoryA]

It's the table references that are the issue - they don't work with closed workbooks.

 

[acramer]

Ahh. Ok. We will try it a little differently and see if we can make it work. Thank you!!!