Solved: Data Validation - character limit

[Beatrix]

Hi All ,

I created a dynamic data validation list by using the formula below in Excel 2010.

=IF(INDEX($1:$1000,ROW(),COLUMN()-1)=CC,IP,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=Pr,IP_1,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=Sc,IP_2,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=Sp,IP_3,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=CDT,IP_4,"")))))

It's working ok. However I have to add more fields into this formula. Because of character limit below formula is not working. I used named ranges to keep it short but I don't know what else I can do to make this formula shorter :(:(

=IF(INDEX($1:$1000,ROW(),COLUMN()-1)=CC,IP,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=Pr,IP_1,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=Sc,IP_2,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=CDT,IP_3,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=Sp,IP_4,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=HI,IP_5,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=ASD,IP_6, IF(INDEX($1:$1000,ROW(),COLUMN()-1)=SLI,IP_7, IF(INDEX($1:$1000,ROW(),COLUMN()-1)=Spc,IP_8,"")))))


your help would be much appreciated.

Regards,
Yeliz

[Bob Phillips]

Create a defined name say of id referring to

=INDEX(Sheet1!$1:$1000,ROW(),COLUMN()-1)

and then use

=IF(id=CC,IP,IF(id=PR,IP_1,IF(id=Sc,IP_2,IF(id=Sp,IP_3,IF(id=CDT,IP_4,"")))))

[Beatrix]

Hi again ..

I forgot, I'm working in excel 2010 but the file is an xls file. Because there is a limit for nested functions in 2003 and previous excel versions so it is another problem :-( is there any other way to create a dynamic data validation list based on 8 nested functions for an xls file?

Regards,
Yeliz

[Bob Phillips]

Yes, but it takes more characters

=IF(id=CC,IP,"")&IF(id=PR,IP_1,"")&IF(id=Sc,IP_2,"")&IF(id=Sp,IP_3,"")&IF(id=CDT,IP_4,"")

[Beatrix]

Hi ,

Thanks very much for your respondS. I did change the formula as you said and wanted to test it for 5 IF functions. The formula is ok however I keep having same error message below since I created named range id which refers to INDEX($1:$1000,ROW(),COLUMN()-1)

the list source must be a delimited list, or a reference to single row or column

I created custom data validation first and then selected list option, it did work for previous formula but it's not working now..It doesn't like something in named range or I don't know what cause a problem :-(

[Bob Phillips]

I did test it, and it worked fine here, so I am a bit lost.

Can you post your workbook with the test you mention there?

[Beatrix]

Please see data validation tab in the attachment, F10:F17 is set for data validation based on the formula below.

=IF(id=CC,IP,"")&IF(id=Pr,IP_1,"")&IF(id=Sc,IP_2,"")&IF(id=CDT,IP_3,"")&IF(id=Sp,IP_4,"")

Regards,
Yeliz

[Bob Phillips]

Yeliz,

I am now confused as to what you are validating for. Can you in explain it, in language not code?

[Beatrix]

Yes sure.. (btw, please see new attachment)

I am trying to create a dynamic dropdown list in column F in data validation sheet. There are 9 different IP lists. What I want is, Column F should come up with the related dropdown list based on data in Column E. If Area Type (Column E) is Pr then dropdown list must be IP_1, If area type is Sc then dropdown list must be IP_2, etc..

When I select Area in column D then area type is filled automatically and column F displays related IP list. I set the data validation in column F by using the previous formula. Please see attachment.
However I can’t extend this formula for 9 lists as there is character limit in data validation. Therefore I named Index formula as id as you suggested, When I copy that formula, it’s not working though..
Regards,
Yeliz

[Bob Phillips]

I ended up with the following formula

=OFFSET(IP_Codes,1,MATCH(E11,Areas,0)-1,COUNTA(INDEX(IP_Data,0,MATCH(E10,Areas,0)))-1,1)

But I also had to add some new names, deleted a lot of existing names, and rejigged the layout on fields a bit. Should be fully extensible now.

[Beatrix]

Hi xld,

Thanks very much for your time and help..I really appriciate it.

the fucntionality of dynmaic dropdown list is not working ok. It's not dynamic. When data is changed in area column, IP dropdown menu doesn't come up with the right list..Can I use a formula instead of E10 and E11 in OFFSET function to make it dynamic?

Cheers
Yeliz

[Bob Phillips]

It works fine for me Yeliz. I changed the first one to HI, and got a restricted list of 5 values.

[Beatrix]

It's so true.. When you change the first one as HI it comes with the right list. However you'll see what I mean if you have a chance to look at the attachment. I haven't changed any functionalities on what you've done for me. I only deleted data in Area column and put some random data. Even E10 and E11 are Pr they come with diffrent lists and last record doesn't come up with any dropdown list. E12 (last record) is Sc and doesn't display any list in F12. If you put data in D13 then F12 would list the options..

Sorry to bother you.. I totally understand if you don't respond..

Regards,
Yeliz

[Bob Phillips]

The formula was slightly wrong.

[Beatrix]

It's working perfect :-) !!! Thank you so much..........

Regards,
Yeliz

[astr0zombi3s]

Hi,Any chance I can see the code used to do this? I am having the same issue of character limit in the data source field for dynamic drop downs. I do not see where any attachment is housed.Thanks,Jeff

[Beatrix]

Hi Jeff ,

Please see attachment. There isn't any code. Dynamic drop downs are based on named ranges and formulas which are used in data validation. Hope this helps:thumb

sorry for the late response.


Hi,Any chance I can see the code used to do this? I am having the same issue of character limit in the data source field for dynamic drop downs. I do not see where any attachment is housed.Thanks,Jeff