Bug 145103 - "Invalid Value." window pops up by cells with Validity check
Summary: "Invalid Value." window pops up by cells with Validity check
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.2.1.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-10-12 23:46 UTC by omui+libreoffice
Modified: 2021-10-13 21:13 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
Amazon SellerCentral Sheet, any English Letter in D25 gives an "Invalid Value." error (988.08 KB, application/wps-office.xlsx)
2021-10-12 23:46 UTC, omui+libreoffice 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description omui+libreoffice 2021-10-12 23:46:48 UTC 
Created attachment 175700 [details]
Amazon SellerCentral Sheet, any English Letter in D25 gives an "Invalid Value." error

In the attached excel sheet (generated by Amazon.com SellerCentral), there are 2 errors that occur with Libreoffice Calc but won't occur with MS Excel.

1. "Invalid Value"
In Cell D25 after "First Name:", enter any name such as "David", Calc will report an error "Invalid Value.".

The cell has a Validity check, which is:

ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

However, English letters should have been in the range but somehow not recognized by Calc.


2. "Re-type Password"
After opening the file for a while (or when one tries to edit other cells such as J13:J16), a window will pop up asking to "Re-type Password", which cannot even be cancelled unless one clicks the "Re-type" button after "...sheet Hash incompatible", enters any password and confirms.

I'm not sure if both problems are related, so could someone please take a look?

Thank you very much.
Comment 1 m_a_riosv 2021-10-13 17:17:34 UTC 
A1 it's empty so
"1:"&LEN(A1) gives 1:0 what it's not a correct address

with D25 instead A1
ISNUMBER(SUMPRODUCT(SEARCH(MID(D25;ROW(INDIRECT("1:"&LEN(D25)));1);"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

I think it works.
Comment 2 omui+libreoffice 2021-10-13 19:53:22 UTC 
(In reply to m.a.riosv from comment #1)
> A1 it's empty so
> "1:"&LEN(A1) gives 1:0 what it's not a correct address
> 
> with D25 instead A1
> ISNUMBER(SUMPRODUCT(SEARCH(MID(D25;ROW(INDIRECT("1:"&LEN(D25)));1);
> "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))
> 
> I think it works.

I confirm changing that did work, as no "invalid values." will pop up. So it appears that this is a wrong formula provided by the sheet maker (Amazon), I'll give this feedback to Amazon.

However, the "Re-type Password" window still pops up, though.
Comment 3 m_a_riosv 2021-10-13 21:13:18 UTC 
Sheet it's protected.

Let's close for now as notabug.