[Solved] How do I create a conditional validation list

[iamoraal]

I am trying to create a validation list from one of two cell ranges based on whether the contents of a cell (formated as date) is a saturday or sunday. The cell in column A will be either a Saturday or a Sunday.
Something like:

Code: Select all

 psuedocode :: 
if(A3 contains "Sat" then select contents from cells D10:D20,E10:E20)

I was checking out the Search function but got a 502 error. I suspect that's because the date is numerical not text.
Help would be appreciated.

[Villeroy]

download/file.php?id=333

[iamoraal]

@Villeroy :: Thanks for the reply. I took a look at the file and learned a few things, but did not find the solution I was looking for. However I realized that I was overthinking the problem. The solution although initially more labour intensive simply required the creation of the lists I needed and the use of Validations "Allow Cell Range" to select the respective lists. By including blank lines I am able to add-modify-delete names to the lists without having to edit the validation rules. An easy and I think elegant solution. By creating a Master Template I now can create a monthly schedule with ease which is updated as I edit the volunteer lists.
I posted the file if anyone is interested.

[Villeroy]

By including blank lines I am able to add-modify-delete names to the lists without having to edit the validation rules.

menu:Tools>Options>Calc>General: "Expand references ..." = ON
Now you expand ALL references when you insert or append cells to a list. References refer to ranges in cell formulas, range names, chart sources, conditional formatting, validation and more.

See viewtopic.php?f=9&t=99846&p=480614#p480614 for more info on this option.

[iamoraal]

@Villeroy :: Nice. Thanks for that.