LOOKUP: change referenced spreadsheet

[Guest]

With the upgrade in Excel, I can no longer use 'Find and Replace All' to
change a spreadsheet referenced in my VLOOKUP statements (everything is in
one file). The 'old' and 'new' spreadsheets are identical except for the
data and number of lines, columns and headings are the same. Now, two
windows appear to select the file and worksheet for each occurance of the
formula - which happens hundreds of times. Previously, I would highlight the
column containing the VLOOKUP formula, select 'Find', enter the old sheet
name and new sheet name, and select 'Replace All'.

 

[Dave Peterson]

I think if you look closely, you misspelled the name of the new worksheet.

Just a tip...when you do this, select a small range first. Then if you make a
mistake, you'll only have to dismiss a few dialogs.

 

[Guest]

Thanks for the suggestion, but the names are spelled correctly. I am merely
changing an FY05 to an FY06 - the rest of the name remains the same. I do
test a small sample of six cells, four of which contain the formula in
question. (The other two sum the rows.) The 'Find and Replace All' worked
fine before updating to Excel 2003.

Any other suggestions would be grealy appreciated, as I have relied on this
form of updating spreadsheets in quite a few of my files.

 

[Dave Peterson]

I ain't giving up.

Check for extra spaces in that worksheet name (maybe leading or trailing)???

And the new worksheet already exists, right???

 

[Guest]

Thanks for not giving up!

I thought that I had checked the blanks, but there was a trailing one!

I am happy once again!

Thank you!

 

[Dave Peterson]

And my faith in excel is restored!

Thanks for not giving up!

I thought that I had checked the blanks, but there was a trailing one!

I am happy once again!

Thank you!