Calc: depended drop down list from other sheet problem

[jankowal]

Hello, i wanted to create a depended drop down list. I have a problems, it only works in this same sheet, when i want use data from other sheet it is not working…
For first drop down list i used standard: data - validity - cell range and i choose this range:

$Sheet1.$D$2:$D$3.

That works, but when i want also copy data to second, depended drop down list i don’t get a score.
I was able to do it in this same sheet and it was like this:

INDEX(F2:G6;;MATCH(B1;F1:G1))

It worked good but how to rearrange it when that every of cell is in other sheet?

I added sample file (i found it in some forum, it is not mine), when it work on one sheet, i just want to create this drop-down list in second sheet.

sample.ods
I use linux ubuntu, LibreOffice 6

[mariosv]

Please attach a sample file, editing your question.And inform your LibreOffice version and operating system. Making easier to someone help.

[mariosv]

Put the sheet name before every range.

INDEX($Sheet1.F2:G6;;MATCH($Sheet1.B1;$Sheet1.F1:$Sheet1.G1))

and then you can copy the formula between sheets.

15803351633953162-1.ods

[jankowal]

Hello @mariosv,
thank you very much for your help and time! I just also changed that B1 is in Sheet2 and it works well

I have also other question in this same area, maybe you will know an answer

For example i will add new columns also named “Animals” and it will contain 5 new names of animals, for example lion, elephant etc.
Is that possible to make a depended drop-down list, that i will have to choose two categories (Animals and Flowers), but when i will chose Animals category, i will have every animal which belongs to this category name, no matter which “Animals” colums?