Excel 2010 Replace worksheet name in formula linked to a different workbook
 

I am trying to replace a worksheet name in a formula linked to a different workbook using Excel 2010.

Current: ='M:\ACCOUNTING\Mike\2010 Sales\6 - Period 2010\06152010\[3 - BoulderWE061510.xls]Weekly Sales'!$E$46

Need to Replace with:='M:\ACCOUNTING\Mike\2010 Sales\7 - Period 2010\06222010\[3 - BoulderWE062210.xls]Weekly Sales'!$E$46

ok so here is my issue. Using Excel 2003 and/or 2007 I simply would use Replace 0615 with 0622 I would have to hit esc and it would give me a #REF! error. Then I would replace 6 - Period with 7 - Period and presto it was updated. Now using Excel 2010 when I try to esc it stops.

Anyone?

Answer: Excel 2010 Replace worksheet name in formula linked to a different workbook
 

In Excel 2010, you can still use the Replace function to replace the worksheet name in a formula linked to a different workbook. Here's how:
[list=1][*] Select the cell containing the formula you want to update.[*] Press Ctrl + H to open the Find and Replace dialog box.[*] In the "Find what" field, enter the old worksheet name (e.g. "Weekly Sales").[*] In the "Replace with" field, enter the new worksheet name (e.g. "Weekly Sales").[*] Click the "Options" button to expand the dialog box.[*] In the "Within" field, select "Workbook".[*] Click "Replace All" to replace all instances of the old worksheet name with the new one.

Note that if the worksheet name is part of a longer string (e.g. "='M:\ACCOUNTING\Mike\2010 Sales\6 - Period 2010\06152010\[3 - BoulderWE061510.xls]Weekly Sales'!$E$46"), you'll need to be careful not to replace any other parts of the string that might contain the same characters. In this case, you could try using wildcards in the "Find what" field to match only the worksheet name (e.g. "*Weekly Sales*").

just a minor note, if you are using excel 2007
make sure the following options is checked in
1. go to excel options
2. advanced
3. when calculating this workbook
4. check in a) update links to other documents b) save external link values

all the best

Thanks! I am using 2010 but checked the excel options and both were checked.

Use FIND and REPLACE this way:

FIND:6 - Period 2010\06152010
REPLACE:7 - Period 2010\06222010

You are now hitting 2 birds with one stone!