Basically, the idea is: creating an option where I can input something into some cells. Let’s say I input “banana” in A2, “apple” in B2, and “5” in C2. I want a dropdown list that let me choose between “banana”, “apple” or “5”, but if I want to include something else, let’s say “melon” then the next time I use a dropdown in this sheet, it will show “banana”, “apple”, “5” and “melon”
Hallo
Its `→Data → Validy →Criteria →… →Cellrange
This creates the dropdown menu, but if I want to include anything else (eg: “melon”) then it shows me an error because it’s not any of the options already included in the dropdown menu (“banana”, “apple”, “5”).
“This creates the dropdown menu, but if I want to include anything else (eg: “melon”) then it shows me an error because it’s not any of the options already included in the dropdown menu (“banana”, “apple”, “5”).”
You need to expand the source list - if you want to use/choose the new item in the future.
…
And you can adjust the settings of the Data Validity cell: Uncheck the option “Show error message when invalid values are entered” on the TAB “Error Alert” of the Data validity settings - if you want to enter a new value what is not present in the source list.
…
The source list will not refresh automatically. You need some macros - if you want to refresh the source list semi-automatically.
do you mean to use a bigger cellrange?
Cool! I didn’t know 
Oh, damned macros 
haha well, thanks anyway!
Yes, a bigger cell range; or a cell range on a helper sheet, where you can insert a new Row inside the source cell range without destroying other small helper tables.
See if this works for you.
1 Like
Nice tutorial, thanks but it requires a different column where I introduce the options. I would like to interact directly with the options by allowing me to introduce a different value in the cell (without getting an error) and adding automatically this option to the list of possible inputs that will be shown in the dropdown menu.
If the Validity-Source is auto-updated by any Input …… it contradicts the meaning of Validity … you get the point ?
3 Likes
I do, this is why I was looking for something else. I like when dropown menus act as suggestions, rather than a limit on the answers that you need to use. I was wondering if I could do something like this on Calc.
In order to work like a database it needs to be a database.
DummyPersons5.odb (65.8 KB)
Open the attached document. Ignore any macro warning for now. It is usable without macros.
Open the embedded form “Collect Person IDs”. The form allows you to add persons from table “Persons” into a table “FK_Persons”. The latter table stores the ID numbers of the selected persons in a forein key column “FK”.
- When you change any person’s data, the change takes affect in the entire database.
- You can’t enter a person twice. Forename, surname and birth date need to be unique. Otherwise the database will reject the new person.
- Listboxes simply work.
- You can display and edit related data from multiple tables.
- If you can’t find a person in the listbox, you can enter names and birth date into the green boxes, save it, go back to the listbox and refresh it by clicking the 2nd refresh button on the navigation toolbar. With macros enabled, the listbox is refreshed automatically with every new person.
The other forms are related to other topics from forum.openoffice.org.
Open the embedded report “Collected Person IDs”. This is a printable and freely layoutable version of stored data.
For macro support, call Tools>Options>Security>[Macro Security…], choose the highest security level and add a trusted directory which is NOT your download directory. Move the database file to that directory.