how to make a conditional AND with limited selection drop down list

Hi all,

I want to make a conditionnal drop down list and also limit the selection to the data input in my source column.
I know how to do these two things separatly, but when I try to use them both it doesn't work.

Here is what I've tried to do : I first name my source column and limit it to the data input : Company=OFFSET('Data'!$A$2;0;0;COUNTA('Data'!$A:$A)-1;1)
Then I use the indirect formula for the conditional list in B2 : Data Validation / List =INDIRECT($A2)
(A2=Company)

But then I cannot select anything.

Does anyone know how to make this work ?

Many thanks!

Both OFFSET and INDIRECT are volatile functions, and as such you can't use them in conjunction with one another. You will need to define Company using INDEX rather than OFFSET.

Hope this helps.

Pete

Thank you for your answer, but I've tried it using
Company='Data'!$A$2:INDEX('Data'!$A:$A;COUNTA('Data'!$A:$A)-1
but it doesn't work as well.

Do you think that I could try something else ?

Thanks a lot

Hi,

I don't understand what I'm doing wrong but it still doesn't work. Here is my file.
Test list indirect.xlsx

Could anyone help me?

Thanks a lot

Help anyone? Thank you!