Bug 134724 - UI - Find in Calc fails when a formula refers to a cell in another sheet's pivot table
Summary: UI - Find in Calc fails when a formula refers to a cell in another sheet's pi...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Find-Search
  Show dependency treegraph
Reported: 2020-07-11 04:29 UTC by Carl Pearson
Modified: 2021-02-04 08:33 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Requested sheet showing bug (657.32 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-08-27 15:30 UTC, Carl Pearson

Note You need to log in before you can comment on or make changes to this bug.
Description Carl Pearson 2020-07-11 04:29:00 UTC
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"

Actual Results:
The "Find" bar at the bottom of Calc displays "Search key not found"

Expected Results:
The cell containing the value that can be seen on the screen should be selected.

Reproducible: Always

User Profile Reset: Yes

Additional Info:
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
Calc: threaded
Comment 1 sora34ce 2020-08-07 18:56:40 UTC
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.
Comment 2 Roman Kuznetsov 2020-08-26 17:27:53 UTC
Carl, please attach example file here
Comment 3 Carl Pearson 2020-08-27 15:30:16 UTC
Created attachment 164758 [details]
Requested sheet showing bug
Comment 4 Carl Pearson 2020-08-27 15:31:08 UTC
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!
Comment 5 QA Administrators 2020-08-28 04:04:34 UTC Comment hidden (obsolete)
Comment 6 eisa01 2021-01-23 20:59:29 UTC
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: / 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
Comment 7 Eike Rathke 2021-01-25 13:04:54 UTC
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.
Comment 8 Heiko Tietze 2021-01-26 11:42:28 UTC
(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
   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).
Comment 9 Heiko Tietze 2021-02-04 08:33:08 UTC
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.