Setup a formula to be completed later

[fb7894]

Here is an interesting problem...

I have a macro that creates a formula that will link to an external spreadsheet. The user will use a file picker to tell the macro the name of the path/workbook.

How can i set up a "template formula" in my workbook?

Example: I want to write a template formula like this

='myLinkedWB'!$H$43

then, the macro will change all my formulas to put in the valid path of the linked wb.
='C:\My File Path\[prototype v3.xlsx]Details'!$H$43

The problem is, Excel will not allow me to write formula #1 because it is invalid. Does anyone have a glamorous solution to this?

[Paul_Hossler]

You could try making it a string by putting a ' in front

'=myLinkedWB!$H$43

Then your macro has to replace

'=myLinkedWB

with

='c:\My File Path\[prototype v3.xlsx]Details

losing the leading ' and replace the myLinkWB placeholder with the real filename and sheet name


If Replace doesn't work, you might have to use loop .Find and use .Formula after fixing the placeholder