Calc's Find function defaults to formulas. All Pivot Table results are Values.
If a cell contains a formuala referencing another cell in a Pivot Table, searching returns "Search key not found". One must use Find and Replace, then change the "Search in" drop-down to "Values".
If sheet "Pivot" contains a pivot table, Pivot.A4 contains the value "My Pivot Value", and Sheet1.A4 contains "=Pivot!A4" and displays "My Pivot Value", using Cmd-F / Edit->Find from Sheet1 and searching for "My Pivot Value" fails with "Search key not found".
Using Alt-Cmd-F / Edit->Find and Replace, changing "Formulas" to "Values" and clicking "Find Next" will successfully select Sheet1.A4
Steps to Reproduce:
1. Create a pivot table ("Pivot") from data in some sheet.
2. Have a formula in another tab ("Sheet1") that refers to a cell in that table.
3. Use "Find" to search for a value that can be seen in "Sheet1"
The "Find" bar at the bottom of Calc displays "Search key not found"
The cell containing the value that can be seen on the screen should be selected.
User Profile Reset: Yes
Build ID: a726b36747cf2001e06b58ad5db1aa3a9a1872d6
CPU threads: 16; OS: Mac OS X 10.13.6; UI render: default; VCL: osx;
Locale: en-US (en_US.UTF-8); UI-Language: en-US
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
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!
[Automated Action] NeedInfo-To-Unconfirmed
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: 220.127.116.11 / 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
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.
* Calc Find toolbar should have "entire cell" checkbox, same as
+ 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.