Find and Replace Help-Values & formulas

[dakotasteve]

Hello everyone. Just wondering, when I use the Edit/Find and Replac
command, I search for an amount I know exists in the spreadsheet (let
say the value I am searching for is 120 (which is the value result f
the formula which multiplies one cell with 4 and the other cell wit
the value 30). I use the command and I get the "Cannot find the data
message from Excel. Is the only way around this is to add a colum
where I do paste special and paste the values from the formulas and th
search for the number I was originally looking for???
Any help greatly appreciated.
Stev

 

[Paul]

Hello everyone. Just wondering, when I use the Edit/Find and Replace
command, I search for an amount I know exists in the spreadsheet (lets
say the value I am searching for is 120 (which is the value result fo
the formula which multiplies one cell with 4 and the other cell with
the value 30). I use the command and I get the "Cannot find the data"
message from Excel. Is the only way around this is to add a column
where I do paste special and paste the values from the formulas and the
search for the number I was originally looking for???
Any help greatly appreciated.
Steve

In the case of a formula, Find is looking within the formula rather than at
the result thereof. So, in your case, if you had 4 in A1 and 30 in B1 and
the formula =A1*B1 in C1, Find would not locate 120. You can see this by
trying to find, say, A1, which it would locate in C1.

So, you are right that you would need to paste the values to search for 120.
But this would not be effective under all circumstances because of rounding
errors. Multiplying integers is OK, but multiplying other numbers can result
in values that are not exact, for example 119.999999999999 or
120.000000000001. Whilst both of these may be displayed as 120, using Find
to look for 120 would only locate the latter, not the former. Your formula
would need to have actually rounded the value to 120 before the Copy/Paste
Values operation.

 

[PegL]

You could change the Lookin option on the Find to look at
values rather than formulas.

 

[dakotasteve]

Thank you! That was an easy solution that seems to work fine.
thought I had tried it in another spreadsheet with an array formula an
it didn't work, but now it appears to do the trick!
thanks again!
Stev

 

[dakotasteve]

Paul,
I used the option PegL supplied which had me change
the "look in" option to "values" and now it seems to work,
though I thought I had tried that before and it didn't
find the value. So maybe we have a solution eh??
Steve