PDA

View Full Version : Setup a formula to be completed later



fb7894
06-26-2018, 08:39 AM
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
06-26-2018, 09:13 AM
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