• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Passive (filename only) reference to VLOOKUP table

polarisking

polarisking

Member
I have a named range in my Macro workbook that is used by a separate workbook. When I distribute both the Macro workbook and the other workbook to others to use, the vlookup reference in the other workbook "breaks" as it appears to be using the full path name back to the macro workbook rather than just the macro file name itself. The VLOOKUP is in the other workbook itself, not in the VBA code, so it's the template workbook that's the issue.

Any counsel would be appreciated.
 
Hi ,

The use of words like template workbook is somewhat confusing ; you have two workbooks , one of which alone is a .xlsm / .xlsb file ( macro workbook ) ; let us call this A.xlsm

You have another workbook , which for want of more information , we can call B.xlsx

B.xlsx has a VLOOKUP formula , which refers to the full path name of A.xlsm ; when you distribute both workbooks to other , obviously the path names of both files will change , and hence the VLOOKUP formula is failing. Is my understanding correct ?

If so , can your users who receive the 2 workbooks , not resolve the external links , since you say the VLOOKUP formula is not in VBA code , but in the worksheet cells ?

If at all you wish to make it path independent , then one way is to use the INDIRECT function and specify only the workbook name ; this will work provided your users will always have both files open , when ever they work on B.xlsx

Narayan
 
INDIRECT (why didn't I think of that) should fit the bill quite nicely. Once again, Narayan, I'm in your debt. Thank you.
 
You must log in or register to reply here.
Back
Top