Updating 2 parts of a Linked Formula

[slantz]

Hello -

I have a spreadsheet that links to dozens of other documents for reconciliation purposes. When we go to a new month, two parts of the linked formula need to be updated. If I only update one part, Excel opens a dialog for me to link to the actual spreadsheet.

Is there a way to simultaneously update two pieces of a linked formula so this doesn't happen?

Excel Version 2007.

Here's an example of what I'm trying to do. I would like to replace all instances of "12 - June" and "20160601" with "01 - July" and "20160701" respectively.

=VLOOKUP("Grand Total",'X:......\12 - June\....\[20160601_.... Bill - )

Regards,

Steve

[Pete_UK]

You can do this by applying Find & Replace (CTRL-H) four times on the new worksheet. You need firstly to select all the cells that this is to apply to, and then you can do CTRL-H and:

Find What: =
Replace With: $$=
Click Replace All

This will turn all the formulae into text expressions, so that you can then do:

Find What: 12 - June
Replace With: 01 - July
Click Replace All

and:

Find What: 20160601
Replace With: 20160701
Click Replace All

and then finally you can change the $$= back to just =.

Hope this helps.

Pete