Dependant lists is not working as before. What's happening

[Scooterza]

Hi there,

I have used the method before in my spreadsheets where I have a primary list and then a sublist is shown that depends on what primary listing was selected. This has worked for me before but now, this doesn't seem to be working! Can anyone suggest why?

I have put my spreadsheet on the cloud. See here.

Many thanks

 

[Supes77]

For apple range exacmple you have:
OFFSET(Lookup!$B$2,0,0,COUNTA(Lookup!$B$1:$B$100)-1,1)

You dont need that - Just the range of values B1:B21 ? Changed that and it worked

 

[Scooterza]

The strange thing is that if you look at the named range called Apple, it does evaluate correctly - it includes the right list of apple phones. And if you then change the data validation from =Indirect(A2) to =Apple, it also works. Indirect(A2) returns "Apple" so why =Indirect(A2) doesn't work but =Apple does is a mystery to me!

 

[Andrew Poulsom]

INDIRECT won't resolve a dynamic reference.

 

[Scooterza]

Thanks Andrew. That explains why it works if referring to the list directly but not when using Indirect.

So if we want to use a list that has a sublist that is dynamic (ie users can add new phones to the bottom of a list of phones and the grows), any suggestions as to how to do this?

 

[Andrew Poulsom]

Maybe this will help you:

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