Issue 80488 - Dynamic Lists are treating blank cells as having 0s in them. See the attached files
Summary: Dynamic Lists are treating blank cells as having 0s in them. See the attache...
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: configuration (show other issues)
Version: 680m223
Hardware: All Windows XP
: P2 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL: http://www.adastragames.com/downloads...
Keywords:
Depends on:
Blocks:
 
Reported: 2007-08-08 22:21 UTC by ken_burnside
Modified: 2017-05-20 11:31 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
Two simple files that show the dynamic list population difference between Excel and Calc. (9.38 KB, application/x-compressed)
2007-08-08 22:23 UTC, ken_burnside
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description ken_burnside 2007-08-08 22:21:05 UTC
On this spreadsheet, the user selects a range in cell B2; this triggers display
of items in cells C2, D2, E2, and F2.

The user can then select, in cells C3, D3, E3 and F3, another variable that
would (in the full spreadsheet) modify data relevant to the cells above it.

The problem I'm having is in the Data > Validity settings in cells C3 through F3
mentioned above.

There are three named ranges in the spreadsheet:

Ordered_List, Blank, and Action Points.

Ordered_List populates the drop-down in B2.

Blank should evaluate to an empty cell.

Action_Points evaluates to the drop-down list that would populate the cells C3
through F3, if the cell immediately above them was NOT blank.

If you examine the files in the URL listing, you will find that cells C, D, E
and F on row 3 has Data > Validity activated, as follows:

IF(E2="";Blank;Action_Points)

So, in plain English - if cell E2 is blank, cell E3 should also be blank.  If
cell E2 is NOT blank, cell E3 should be populated by the Action_Points drop down.

The problem is that that first check is not working, but does in Excel, and I
use it a LOT in Excel.

The zip file listed in the URL has two copies of the spreadsheet, one for
OpenOffice, the other for Excel; this works the way I want it to in Excel, but
not in OpenOffice, and I need it to work the same way in both.
Comment 1 ken_burnside 2007-08-08 22:23:02 UTC
Created attachment 47408 [details]
Two simple files that show the dynamic list population difference between Excel and Calc.
Comment 2 frank 2007-09-19 15:46:00 UTC
Seems to be fixed for 2.3. Only difference is that we show 0 instead of a blank
cell which is intended.

Frank
Comment 3 frank 2007-09-19 15:46:28 UTC
closed fixed
Comment 4 ken_burnside 2007-09-19 16:07:14 UTC
I need to clarify something:

====
Seems to be fixed for 2.3. Only difference is that we show 0 instead of a blank
cell which is intended.
====

The fact that it shows 0 rather than a blank cell is exactly the problem I need 
addressed, and renders OO.o 2.3 useless for a lot of spreadsheets that I use.

A blank cell is evaluated very differently from a cell with a zero in it for a 
lot of spreadsheet uses, including several that I use regularly.  Having 
blank=0 breaks a lot of text form formatting, and prevents dynamic lists from 
error checking properly.

Please fix this so that blank is distinct from 0.
Comment 5 frank 2007-09-19 16:21:51 UTC
Hi Niklas,

please comment on this one.

Frank
Comment 6 ken_burnside 2007-11-25 22:29:41 UTC
I have not seen any further action on this.

To re-iterate, I need a blank cell evaluating as "empty", not as "0", for a lof 
of functions I run to work.

Any progress to report?
Comment 7 kpalagin 2008-01-28 09:35:19 UTC
ken_burnside,
I do not see any difference between Calc 2.3 and Excel 2003 with your files.
Please elaborate.
Comment 8 mcintoshcraig 2008-02-05 03:59:06 UTC
I've duplicated this to the point where I see the discrepency ken_burnside seems
to be talking about.

Steps (using either of his files):
1 - Use the drop-down menu in B2 to set that element to '3'. 
2 - C2, D2, and E2 will automatically be set to '3', '2', and '1' respectively.
3 - Now click on the drop-down menu at F3. Only one option shows up for the value.

Doing this in Excel, the option that shows up in step 3 is blank (or, rather,
there is no option). In OO Calc, it is '0'.

This difference will show up in more cells, depending on how B2 is set. If set
to 2, then E3 and F3 will behave differently. If set to '1', then D3, E3, and F3
will all behave differently, based on whether the cell above them is empty.

Hope that clears it up. I've duplicated this on Linux (Ubuntu 7.10) for OO Calc
(OpenOffice 2.3), and Windows (Vista, Office 2007 SP1).
Comment 9 kpalagin 2008-02-05 07:57:53 UTC
Confirming as per mcintoshcraig.
Comment 10 Marcus 2017-05-20 11:31:19 UTC
Reset assigne to the default "issues@openoffice.apache.org".