Active File Path from text string

  • '=&'''N:\INVOICES\Store A\[12-30-19.xlsm]Cost'!$L$109

    How can I create an active path from these cell contents using a formula or user defined function?

  • ?

    '=&'''N:\INVOICES\Store A\[12-30-19.xlsm]Cost'!$L$109 isn't a valid formula.

    If you mean you have formulas returning text which look like external references, then unless the workbooks to which those external references pointed were all open in the same Excel session, the only way to turn those formulas into actual external references would be to copy those cells, paste-special as values then replace = with = in those cells (yes, replace = with itself, which has the effect of reentering the text constants as formulas). There's no other simpler way to do this, though if you need this often, you may want to create a macro in your PERSONAL.XLSB for this.

  • This is awesome! A file explorer pop-up does launch for each cell in the range (40 in my case) as each external reference becomes "active", but I sense there is no way to avoid that.

    This was my first post on this board and I really appreciate the response!! Happy New Year!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!