Replace menu command not working

[RS]

I’m having problems with the Replace menu command on an unprotected workbook
in Excel 2000. I’ve set up one of the worksheets whereby I’ve made the cells
refer to the raw data on another tab in the workbook, that way I only grab
the data I want. Using the Find command (Ctl-F), it finds the data on this
worksheet. However, if I try and use the Replace command to find data on
this sheet, it says the data can’t be found (even though I can see it).

Example: in cell K2 of this worksheet I have the formula: =import!AD1. The
result displays the name of a company. Using Ctl-F, finds the occurrences of
this company. However, when using Replace (Ctl-H) to find this name, Excel
says that it can’t find it. Is this because what is displayed is simply the
result of a formula telling it to look on another sheet and the Replace only
sees the formula, not it’s result? I know that when using the Find command,
I need to select Look in Values to find the company name, but when I
immediately click Replace (either from the Find box or directly by type
Ctl-H), that option isn’t there. The “Find entire cells only†checkbox is
not checked. What’s wrong here?

 

[RichardSchollar]

I'm afraid you'd need to amend the source data (ie the Import! sheet)
- you won't be able to do what you are attempting on the formula
sheet. If you activate the Replace function, you'll see that you
don't have an option to Look In values - only Formulas.

You'd have to copy and paste values on your formula sheet and then run
the Find/Replace to get this to work (and then you obviously lose your
formulas).

Richard

 

[RS]

Thanks Richard. I figured this might be the case.