Calc: depended drop down list from other sheet problem

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:


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:


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.

I use linux ubuntu, LibreOffice 6

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

Put the sheet name before every range.


and then you can copy the formula between sheets.


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

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?