FIND does not find ...

[paulcroft]

I have recently migrated to Excel 2010 from Excel 2000. Love the ribbon - NOT.

However, besides that little bit of fun I am having trouble using the Find menu and wonder if some kind soul can assist.

If I want to find a value on the open spreadsheet, e.g. £1,620.00, I'd expect to use Find and enter 1620 and for the cell containing that value to be highlighted. This works so long as the value in the cell actually is 1620 but it does not work if the value shown in the cell is the result of a formula, e.g. (=SUM(C21+D21+E21)). In order to find the cell I am looking for I need to search for one of the actual values in the formula itself. Is this the way it's meant to work? I don't recall having this problem in Excel 2000 and it makes it extremely difficult to find an invoice if I only know the gross value and not one of the intermediate values.

The "Options" I have set are (a) No format set, (b) Within Sheet (c) Search by rows (d) Look in Formulas (e) Match case and Match entire cell contents boxes are unchecked. I've tried other settings to no avail.

I'm sure it's something simple I've overlooked and would welcome some advice.

Many thanks

Paul

[Richard Buttrey]

Hi Paul,

Persevere with the Ribbon. I suspect most of us found it daunting at first and had similar thoughts, but it really does grow on you.

When using the Find functionality, you need to click the Options button and then select 'Look in Values' rather than 'Look in Formulas'

Regards

[Domski]

Change the Option Look |n to Values and it should work fine.

Dom

[MarvinP]

Hi Paul and welcome to the forum,

The default for Find and Replace is to Look In: Formulas. You need to click on the Options Button on the dialog and change it to Values.

See http://www.uwec.edu/help/Excel07/ws-find.htm for pictures.

[paulcroft]

Hi Richard, Dom and Marvin

Many thanks for your superfast replies. You're right of course. I had tried this before with no success but, since your responses were all the same, I persevered and found that the formatting was the problem I was looking for 1680 and 1680.00 but the box was formatted as currency with a comma separator and red negative so it needed the search value to be entered as 1,680.

I note that the find box defaults back to Formulas after I close and reopen Excel so gone are the days of a quick CTRL+F and just enter the search value then :-(

Paul