Using IFS with Validity in Calc

[pprobus]

Libreoffice Version I am currently using: 7.6.4.1, although the issue I am having was also showing up in the previous version I was using (7.4.?.?). I updated to the latest to see if it may have been a bug.

What I am trying to do is to create a dynamic selection list using validity and named ranges. The idea is to select the range shown in the Validity in column B based on what is in column A. My validity formula that is having issues is something like this:
IFS(A1=“Text A”,Range A, A1=“Text B”, Range B, etc)

“Range A” ,etc. are named ranges with varying numbers of items in each. For example, Range A has 10 items listed (say, Sheet2.A1:A10), Range B has 36 items, Range C has 15 items, etc.

If I use the IFS in my validity, I will get 26 “#N/A” after the 10th item when Range A is selected or 21 “#N/A” after the 15th item when Range C is selected. Otherwise the formula works fine.

Just for a test, I tried a nested IF formula in place of the IFS and it works perfectly at adjusting the number of items shown in a validity with no “#N/A”.

While the nested IF is a workaround, it’s clumsy and easy for me to mess up (which I have done, I got the IFS right the first time, minus the issue I am having above, the nested IFS took quite a few tries). Is there any way to use the IFS function and have it vary the length of the list shown in a validity? Or is that just how IFS works in Validity?

I tried selecting, under the validity settings, at skipping blank cells. That did not work.

Thank you for any help.

[Villeroy]

http://forum.openoffice.org/en/forum/download/file.php?id=333

[pprobus]

Thank you for your quick response. I will have to add some additional columns to my “helper sheet” to make this work, but I think this is the simplest fix.

Thanks!

[pprobus]

OK, after trying this solution, I am running into an additional problem that this is not working. Here is what I am trying to do:

I have one named range: Subject which contains Metalworking, Woodworking, Sheetmetal, etc.
The other named range is Machine, which contains MWMachine, WWMachine, etc.
However, those **Machine are also named ranges, For ex. MWMachine contains Lathe, Milling Machine, etc. WWMachine contains Tablesaw, Jointer, etc.

When I try to use the Index-Match in the validity, I will get the correct value for the cell with the correct name but I can’t figure out how to get it to show the range for that named range.

Ex. If I select “Metalworking” for Cell A1, Cell B1 will display “MWMachine”, when I select the down arrow, instead of providing a range of values like “Lathe”, “Milling Machine”, etc. I tried to use the Indirect function both with the Index-Match formula inside it and also using the Index-Match to write the value to a blank cell and using Indirect on that cell value, typing the value “MWMachine” into a blank cell and Indirect(cell) and nothing works. I assume that the Indirect function can’t be used in a Validity?

Just as a check I tried a test to attempt to directly write the name of the range by taking the answer in Cell A1, using the Left function to only use the 4 letters on the left of the word and then appending “Machine” to it, for example, A1 = “Metalworking”, B1 with the validity Left(A1,4)&“Machine” will give me the Validity choice of “MetaMachine” instead of the new range of “MetaMachine” I created for this test.

Is there an “Indirect” like function that will take a text from either an Index-Match or joining two texts together to work with a Validity? The only thing I can get to work is either a nested IF or an IFS function, although the IFS gives me the issue in my original question.

Edit: Uploaded a sample of what I am attempting to do in case what I wrote in my follow-up is not clear. The Index-Match column should match those in either the IFS or Nested IF columns, but it does not provide a list in the validity like the IFS/Nested IF, but those don’t match each other since the IFS shows too many choices for the two ranges with only 3 choices, which can be seen too.

Thanks.
Test-Spreadsheet.xlsx (6.4 KB)