VLookups Don't Update When Source Workbook is Opened via VBA

[mcmehrtens]

I have a main workbook that does calculation on data that is sourced from a separate workbook. I created a button on the main workbook that opens the source workbook for user convenience (that way they don't have to go digging through directories to find the source file).

I assigned the following macro to the button to open the file:

Please Login or Register  to view this content.

My problem: the links in the main workbook still show a #REF! error even after pressing the button to open the workbook. If I open the source workbook manually, the links update fine, but using the VBA macro doesn't update the links, despite the workbook being opened.

I've tried pressing the refresh all button, I've made sure the links and formulas are set to update automatically, I've even tried retyping the formulas in the cell and it still doesn't update.

I believe the #REF! error is occurring in the INDIRECT function within my main workbook links, but it could be the VLOOKUP, I'm not totally sure. Just very lost at the moment.

EDIT: A little more trouble shooting; problem definitely seems to lie in the INDIRECT function. I can retype a VLOOKUP into a different cell using a hard coded reference instead of the INDIRECT and the VLOOKUP works fine.

Cheers,
Matthew Mehrtens

[Glenn Kennedy]

No idea... but just a random thought. INDIRECT may not recalculate unless something changes in the destination sheet. Not a perfect solution, but as a starter can you add a character to a redundant cell on your destination sheet, and then delete it again - as part of your code - in an attempt to get INDIRECT to recalculate?

[Glenn Kennedy]

If the destination file is open and then you open the source file (without having done anything else) does the INDIRECT recalculate and work?

[mcmehrtens]

Yes! If I manually open the file with all my data in it, the calculator spreadsheet automatically updates all of the values. Additionally, if I manually open the data spreadsheet and then open my calculator spreadsheet, it updates fine as well.

It's only when I use the VBA macro that's displayed in my original post that the spreadsheet won't update. It's like Excel doesn't realize that the data spreadsheet is open. But to throw an extra wrench into the puzzle, if I hardcode a file reference to the data spreadsheet into the VLOOKUP formula, it works fine. So, it has to be something with the INDIRECT formula, right?

I tried your solution about modifying something on the data spreadsheet and then deleting it, but that didn't seem to change anything.

[Glenn Kennedy]

I know ABSOLUTELY nothing about VBA... so I can't comment about it with anything other than TOTAL ignorance. But in my blissful state of stupidity, my guess it's your VBA. Can you add a line to the code to force a recalculation in your destination sheet? but that's pretty much what you already did....

[mcmehrtens]

I tried that as well... I tried adding the line:

Please Login or Register  to view this content.

to the end of the macro, but alas, nothing ):