Does Calc Have a Data Verification/List Option?

Discuss the spreadsheet application

Does Calc Have a Data Verification/List Option?

Postby jdanniel » Sun Apr 06, 2008 11:19 pm

Hello.

I'd like to know if Calc has a Data Verification feature, similar to Excel.

What I'm trying to do is create drop-down menus in a column, with pre-entered information.

What I do in Excel is this: I select a portion of a column off to the side, and enter information in the cells. When I select Data Verification/ List, I select those cells.

I then select a column (Column B, for example), and activate Data Verification. Now, every cell in Column B has a drop-down menu, containing the information in the cells off to the side.

Does Calc offer something like this? If so, what is it, where is it located, and what steps do I take to enable it?

Thank you very much! Jd
jdanniel
 
Posts: 67
Joined: Tue Mar 04, 2008 8:25 pm

Re: Does Calc Have a Data Verification/List Option?

Postby Hagar Delest » Sun Apr 06, 2008 11:54 pm

Look at Data>Validity menu.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
AOO 4.1.7 on Xubuntu 20.04 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28998
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Does Calc Have a Data Verification/List Option?

Postby jdanniel » Wed Apr 09, 2008 12:46 pm

I need some help with this.

I can create a drop-down menu using Data/Validity/List easily, no problems...but when I try to ADD entries to the list, I don't see the ORIGINAL entries, and have to type them in all over again.

Here's what I did:

I highlighted an entire column, then went into Data/Validity/List.
In Criteria, I selected Allow/List.
I unmarked Allow Blank Cells, and marked Show Selection List, and Sort.

Then, I typed some items in the Entries box.

So far, no problems. The drop-down menu appears in every single cell in the column.

But...if I want to add entries to the list, I have to start from scratch. The entries I typed in originally are not there.

Do I have to type in everything from scratch, each time I want to add entries to the list? Can this be prevented?

Thank you!

Jd
jdanniel
 
Posts: 67
Joined: Tue Mar 04, 2008 8:25 pm

Re: Does Calc Have a Data Verification/List Option?

Postby Hagar Delest » Wed Apr 09, 2008 1:45 pm

Works fine for me but when you select the cells range, the cells have the same formatting. So if you include in the range a cell without this initial validity condition, it won't work. Note that if you use the paintbrush (toolbar) to copy/paste formatting, then you can include the new cells.
AOO 4.1.7 on Xubuntu 20.04 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28998
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Does Calc Have a Data Verification/List Option?

Postby Villeroy » Wed Apr 09, 2008 1:52 pm

Insert new cells into the existing list in order to expand all references to that range (formulas, validations, conditional formattings, range names, database ranges, charts, ...).
There is a related option Tools>Options...Calc>General:"Expand references on insertion..." which basically means: Adjust references even when I insert neigbouring row(s) at the bottom or neigbouring column(s) right of the existing range.
And then there is a simple and handy macro which takes care about almost all of the details of vertical list expansion:
[Calc, Python] expand/shrink list ranges
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Does Calc Have a Data Verification/List Option?

Postby Hagar Delest » Wed Apr 09, 2008 2:03 pm

Villeroy wrote:Insert new cells into the existing list in order to expand all references to that range (formulas, validations, conditional formattings, range names, database ranges, charts, ...).

But the list data doesn't come from a cell range, they have been entered manually in the list box dialog.
AOO 4.1.7 on Xubuntu 20.04 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28998
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Does Calc Have a Data Verification/List Option?

Postby Villeroy » Wed Apr 09, 2008 2:24 pm

Could be one reason in favour of validation by range. Btw: It is quite easy to spoil your sheet with dozends of different (but non-obvious since invisible) validations and formattings. My add-on "SpecialCells" detects all of them http://ooomacros.org/user.php#221020
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Does Calc Have a Data Verification/List Option?

Postby Hagar Delest » Fri Apr 11, 2008 2:23 pm

I've had a look at your macro and it does indeed a great job!
I've translated it in French, here are the related files.

 Edit: I'm making the screen shots also, will post the new files later. 

BTW jdanniel, is your problem solved?
Attachments
SpecialCells.0.7.1.uno-fr.zip
(26.88 KiB) Downloaded 77 times
AOO 4.1.7 on Xubuntu 20.04 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28998
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Does Calc Have a Data Verification/List Option?

Postby adnhk » Sat Apr 12, 2008 9:56 am

Hi everyone,

Apologies to hijack this thread, but I realised that this thread is about Data Validity, which is also an issue that I've discovered on my spreadsheet in another thread. Can some experts in Data Validity help to solve this mystery?

viewtopic.php?p=20246#p20246

I have a sample excel spreadsheet, and tapping on the drop down list to choose any other countries does not yield any results in OO, but in Excel, this works fine (this sample spreadsheet only have figures for ALL, China and HO from the pull down list.). Hence seems like something is not right with the conversion when I open up this spreadsheet in OO. Oh, in order for the calculations to work in OO (it only works with ALL being selected), we need to:

# Enable the use of regular expressions within formulas
- Select menu item Tools - Options
- Expand branch OpenOffice.org Calc - Calculate
- Tick Enable regular expressions in formulas

Can some kind expert please take a look at this spreadsheet and advise what is wrong with the conversion of Data Validity? Can anyone advise if this is a shortfall or a bug in Calc's implementation of Data Validity? And if so, where and who can I contact to report this shortfall or bug?

I am attaching the sample spreadsheet here for easy reference. Thanks a million in advance.
Attachments
Excel_Vlookup_SUMIF_test.zip
(77.76 KiB) Downloaded 88 times
adnhk
 
Posts: 22
Joined: Thu Mar 27, 2008 1:06 pm

Re: Does Calc Have a Data Verification/List Option?

Postby Villeroy » Sun Apr 13, 2008 12:33 am

adnhk wrote:Can some kind expert please take a look at this spreadsheet and advise what is wrong with the conversion of Data Validity?

See other thread.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 33 guests