I do admit that this can be hard to track. I had a similar issue with a previous error involving sheets. As you said, it does work when tracking values, but sometimes it doesn't work either.

Carl, please attach example file here

Created attachment 164758 [details]
Requested sheet showing bug

Sure thing.

Try doing a regular Edit/Find for any value seen in the "Report" tab.

The issue seems to be that regular Edit/Find cannot "see" the result of formulas which are themselves references to cells in another tab.

Searching via Edit/Find and Replace *does* work, if you change "Search In" from "Formulas" to "Values".

Unfortunately, there is no way to switch between "Formulas" and "Values" in the regular Edit/Find window, or in the find toolbar at the bottom, nor is there (to my knowledge) a way to default Calc to search by values.

If it wouldn't break things, allowing regular Find to search both formulas -and- values would seem to solve this issue.  Not sure what that would do to performance though, as it may end up effectively searching twice.  Though either way is pretty quick already, so that may not be too much of a hit.

Please advise if there's anything else you need.  Thanks for reaching out!
-- 
Carl

I can confirm your behavior

This is different from Excel, in Excel the find bar defaults to searching for "values", not in the "formulas". The Excel find dialog remembers the last used option, but if you use the find bar it is reset back to "values"

I guess the UX team should consider whether the default behavior in LO should change.

This is probably not macOS specific

Version: 7.1.0.2 / LibreOffice Community
Build ID: 53d68d29d90fd16448721a60aad68c28ff0809f5
CPU threads: 4; OS: Mac OS X 10.14.6; UI render: default; VCL: osx
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

First off, the "Formulas" vs "Values" label is a terrible naming, but it's probably due to the terrible naming that Excel introduced and its users are used to. What it actually does and the difference is that Formulas searches the *cell content*, be it literal strings, numbers or formula expressions; and Values searches the unformatted *display value*, literal strings, numbers or results of formula expressions.

Now if the default in Find&Replace was Values (display value) instead of Formulas (content), a Replace operation would destroy any formula expression where the result matched the Find, i.e. Find the result display value but Replace the formula expression that calculated it => the formula is lost and replaced by the literal replacement value. You certainly don't want that to be the default. Specifically, even worse, you don't want to be the default that a Find would match either the content or the result.

The Find&Replace dialog remembers the Values/Formulas setting though and equally may trap the user in the next operation once Values was used.

The Find toolbar only searches in cell content (Formulas).

Confusingly the Formatted Display option searches displayed strings of *literal* cell content and thus *ignores* formulas and their results (which is logical, but..)

Personally I'd always expect a quick find (i.e. toolbar) to search content instead of display values, but YMMV. Though I'd also expect that if I check Formatted Display then formula results were searched as well, not the formula as cell content. For both the toolbar and Find&Replace dialog.

(In reply to Eike Rathke from comment #7)
> Personally I'd always expect a quick find (i.e. toolbar) to search content
> instead of display values, but YMMV.

Me expects rather the displayed value. Thinking about an option but we decided in the past to keep the quickfind simple.

2020-10-22
 * Calc Find toolbar should have "entire cell" checkbox, same as
   Find/replace dialog
   + https://bugs.documentfoundation.org/show_bug.cgi?id=133833
   + add this option or keep the quickfind bar simple?
   + no strong preference, quite full right now (Cor)
   + would like to see highlight in the quickfind bar and agree to
     rather keep it simple (Sascha)
   => resolve as WF

Similar requests and decisions for Writer in bug 129469 (Whole words only).

We had this topic in the design meeting. Sascha thinks the default search should be for values (having Benjamin-users in mind) with an option for formulas (for the Eve's). And maybe rename "Values" into "Values and Results".

Since options are odd for the quickfind bar, I would think first about a combined search. Meaning to find Values/Results, Content and Notes.

This bug appears to have been fixed, but it looks like a new one was spawned.  Please advise if what appears to  be the related bug should be opened as a separate item.

CMD-F does now appear to match either values -or- formula results, instead of just values, which seems to be more intuitive.

However, the Edit/Find and Replace dialog box has an issue, one which I don't recall presented previously.

As a test to see if searching from a tab (not the Find and Replace dialog box) worked as desired, I again had a sheet with formulas pointing to cells in another tab, along with a hand-entered value in another cell of the sheet that was the same value as the referenced ones.

In Sheet1:
Cell H10 had the entered value "20.06"
Cells E17 and E18 were formulas - "=Pivot.E17" and "=Pivot.E18" respectively - and the values of those referenced cells were also "20.06"

In Edit/Find and Replace:
"Find" contained "20.06"

When "Search In" was set to "Formulas", only H10 was found.
When "Search In" was set to "Values", all three  cells were found.

It now looks like when "Search In" is "Values", behavior is the same as CMD-F, with either hand-entered values or the result of a formula is matching.

However, when "Search In" is "Formulas", the resulting behavior seems backwards.  Only cells containing actual hand-entered values are being found, and cells that actually contain formulas are being ignored, even if their resolved value is the same as that which is being searched.

For what it's worth, having the referenced data be part of a pivot table did not seem to matter.  If other cells in sheet "Pivot" not actually in the pivot table contained the same hand-entered values, they were not found either when "Search In" was set to "Formulas".

Cells A1:A3: 1,=1,=1+1 quickfind (ctrl+F) finds A1 and A2 regardless if Formula is set in the F&R dialog (ctrl+H), which then includes A3.
The tooltip at the quickfind toolbar says "Find text in values, to search in formulas use the dialog". Sounds good to me (being against additional options in the quickfind dialog).

The F&R dialog has Formula returning A1 as well and Values. Should probably renamed "Formulas and Values" to make it more clear. Or maybe "Values Only" is enough; but then Comments just don't fit. It's actually a radio button Values vs. Comments with a checkbox underneath Values to also serach in Formulas. Wouldn't be an appealing UI, however. What do you think?

Adding =A10 to the list returns this item if A10==1 but also if A10==2. The question here is whether the _formula_ should be analyzed as text. Perhaps better discussed (and resolved) in a different ticket.

OK, this ticket looks closed, will report the behavior in the F&R dialog on a new ticket.

(It still seems backwards in the F&R dialog, only finding hand-entered values when "Formulas" is selected, but both formula results and values when "Values" is selected.  Don't think the  LO version matters here, but while this was originally reported with v6.4.4.2, am on 7.5.0.3 now).

Oh, and thanks for pointing out the quick-find tool-tip verbiage.  Hadn't noticed it before, so  don't recall if it was there in v6.4.4.2...

Take care!

"Works for me" is the more appropriate resolution. ("Fixed" is for when a patch is identified.)