Find and Replace

B

Betty Csehi

I have a worksheet with this formula in Col A1 thru A50: ='Mar Srv Rpt '!D62
(It's referencing another open worksheet)

I want to hi-lit the formula and do a Find on Mar and Replace with Apr.
However, when I do that, Excel opens windows explorer and wants me to
"update values" and then gives me an #ERR in the Cell I'm working in.

Is this a formatting problem on my part?
 
B

Bernie Deitrick

If the workhseet is in the same workbook, and the sheet is named "Apr Srv
Rpt " (note the space on the end), then it should work fine.

Otherwise, you are 1) changing the link from an intra-workbook link to an
inter-workbook link
2) trying to create a link to a non-existent sheet ("Apr Srv Rpt " (with
a space at the end))


If it is case 1, you should replace Mar with [WorkbookName.xls]Apr

If it is case 2, you need ot be more consistent in your naming conventions.

HTH,
Bernie
MS Excel MVP
 
J

Jim Rech

Does this happen if a sheet named 'Apr Srv Rpt ' already exists? I don't
like spaces in sheet names generally but especially trailing spaces, which
can lead to "spelling errors".

--
Jim
|I have a worksheet with this formula in Col A1 thru A50: ='Mar Srv Rpt
'!D62
| (It's referencing another open worksheet)
|
| I want to hi-lit the formula and do a Find on Mar and Replace with Apr.
| However, when I do that, Excel opens windows explorer and wants me to
| "update values" and then gives me an #ERR in the Cell I'm working in.
|
| Is this a formatting problem on my part?
|
|
 
Top