Using IF function in Data Validation

Question

I am currently modifying a worksheet that I made in LibreCalc so that it works in Excel, and as such I encounter small problems every now and then.

I have defined three areas, "ProduktSort", "TankSort" and "Index", Index varies with user input, and now I want to create a dropdown menu through data validation, whose output varies with Index as input.

I go to data validation, choose list and input the formula "IF(Index=ProdukSort;TankSort)". In LibreCalc, this returns every instance in the list "TankSort" where "Index" equals the list "ProdukSort", in Excel however, it returns "Source currently evaluates to an error".

So what am I doing wrong, and how do I fix it. I've searched around and found other ways to create the dropdown menus, but none of them can be used with the setup of the worksheet. I have the three areas to work with, and that can't be changed sadly.

And is there any way to attach an example to ease the understanding? Here's a picture for now. Apologies, but it's in Danish, HVIS is IF and the error says "Source currently evaluates to an error".

Answer 1

In Excel, it is a little more work up front:

http://www.contextures.com/xlDataVal02.html

Answer 2

Is this the only way to do it in Excel? It requires me to completely rework the setup, which a lot of other variables depend on, so I would LOVE to avoid it.

Answer 3

You can create a dynamic named range with formulas that extract the correct values - for example, in H1, enter "DVList" In H2, array-enter (enter using Ctrl-Shift-Enter) the formula

=IFERROR(INDEX(TankSort,LARGE(IF(ProduktSort=Index,ROW(ProduktSort)-1),COUNTIF(ProduktSort,Index)-ROW(A1)+1)),"")

and copy down until it returns blanks.

Then create a dynamic named range named DVList, using the formula:

=OFFSET(Sheet1!$H$1,1,0,COUNTIF(Sheet1!$H:$H,">0"),1)

and as your DV list source, use

=DVList

Of course, my default sheet name is Sheet1 - change that to your actual sheet name.

And you may need to change my separator , to ;

Note, too, that if you want strings and not numbers from TankSort, then you need to change

COUNTIF(Sheet1!$H:$H,">0")

to

COUNTIF(Sheet1!$H:$H, ">""")-1

Answer 4

Thank you very much