Dropdown list (strange case for me)

[vkarafil]

Please take a look on the .ods file. It’s a “sample” from a school timetable. I need to create a dropdown list for each one of the blank (empty) cells, which appear with a green background in this sample file. The difficult part for me, is that I need this dropdown list to include all the values (i.e. teachers) of the same column, but by ignoring the values with brackets. For example, the dropdown list of the cell F18 should take the values from TRAD1, ELECT4, PIANO6, ELECT1, in the form {TRAD1}, {ELECT4}, {PIANO6}, {ELECT1}, plus a blank (empty) value. The values (TRAD1), (ELECT1) for the cell F18 should be omitted. I need the same dropdown list for the blank empty cells of the same column (F). I.e. F21, F22, F27 and F32.
For the cells J20 and J26 I need the dropdown lists with {PIANO5}, {PIANO4}, {PIANO1}, {PIANO2}, plus a blank (empty value). The values in parentheses should be omitted.
In all the above cases the “X” values should be omitted from the dropdown lists.
Is there any way to achieve this result with LibreOffice Calc?
Thanks in advance.

timetable_sample.ods (24.7 KB)

[fpy]

@vkarafil

Is there any way

if I understood well, nothing so strange; you may amend your title not to mislead potential readers

@vkarafil

dropdown list

usually Validity of Cell Contents

@vkarafil

brackets

rather parenthesis first ?

@vkarafil

in the form

possibly (even if, yeah, cryptically) : =REGEX(REGEX(F18, "(^\w{2,})","{$1}"), "^[^{].*", "")

timetable_sample_dd.ods (28.8 KB)

[vkarafil]

Wow! Thank you for responding! I am going to check this approach very carefully, since I really needed it for handling timetabling problems.
If I understand correctly, the user has to prepare the validity of each blank cell separately. Is this correct? If yes, your approach is very good, but the user would need too much time in order to prepare the whole timetable. EDITED: After making some tests, I think that the user can prepare the validity of selected cells from the same column at once…
Would a programming approach be a better solution? Of course, in case this is possible. For example writing some kind of script which checks the timetable for blank cells and prepares the validity of each cell accordingly to the pattern I described in my post. Which programming language would you recommend for this problem?
And one more question: Is it possible to include double (or triple, etc.) values just once? For example, there is no real reason for having the value {ELECT1} doubled. Including {ELECT1} just once is enough.
Thank you again!!!

P.S. I modified the title of my post… You are right!

[fpy]

@vkarafil

validity of each

actually, if you use absolute row in F18 validity range => O$18:O$40
you can copy it wherever in your table.

@vkarafil

just once

yep, e.g. Is there a unique function in LO Excel - #5 by Zizi64
but more likely reveals that your data should be better organized first, to be used in a simpler (less strange) way
you may get inspired from existing examples : Timetable with Calc