Does Calc Have a Data Verification/List Option?

[jdanniel]

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

[Hagar Delest]

Look at Data>Validity menu.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.

[jdanniel]

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

[Hagar Delest]

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.

[Villeroy]

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

[Hagar Delest]

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.

[Villeroy]

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

[Hagar Delest]

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?

[adnhk]

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?

http://user.services.openoffice.org/en/ ... 246#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.

[Villeroy]

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.