PDA

View Full Version : Excel Data Validation List



Beatrix
09-30-2010, 06:11 AM
Dear All ,

I have been trying to set up a dynamic data validation list in 2010 excel. However it must be supported by 2003-2007 versions of Excel.4601

I used the formula below for dynamic data validation list in column E in tab M.

=IF(INDEX($1:$1000,ROW(),COLUMN()-1)=CC,N,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=Pr,O,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=Sec,P,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=Sp,Q,IF(INDEX($1:$1000,ROW(),COLUMN()-1)=un,X,"")))))

Using cell references in this formula is causing a problem as it's not supported by previous versions. I get the message below when I want to save it as xls file.
One or more cells in this workbook contain data validation rules which refer to values on other worksheets. These data validation rules will not be saved.

Therefore I used named ranges in this formula, however INDEX($1:$1000,ROW(),COLUMN()-1 part still causing a problem.

To solve this problem I changed the formula as =IF(INDEX(M,1,1)=CC,N,IF(INDEX(M,1,1)=Pr,O,IF(INDEX(M,1,1)=Sec,P,IF(INDEX(M ,1,1)=Sp,Q,IF(INDEX(M,1,1)=un,X,"")))))

However in this case, I need to get row number dynamic in index function as dropdown menu shows the same list in every row. I don't know how to do it.

Your help would be much appreciated. Please find the spreadsheet attached.

Regards,
Yeliz

Bob Phillips
09-30-2010, 06:23 AM
Yeliz,

Can you post the workbook so that we can see what the formula does. The error suggests it to be referring to another sheet, but I can't quite see that in the formula, so something tangible would help.

Beatrix
09-30-2010, 06:45 AM
Hi James,

thanks so much for your quick respond.. I attached the workbook in my first message, please click on hyperlink called data validation list to get into the workbook..

Regards,
Yeliz

Beatrix
09-30-2010, 07:42 AM
Hi James ,

I tried to attach the workbook again..

Cheers,
Yeliz

Beatrix
09-30-2010, 07:55 AM
Hi James ,

Hope you've got the workbook? I can't attach the file again as it gives an upload error saying that the workbook is already attached in this thread..

Regards,
Yeliz

Bob Phillips
09-30-2010, 09:08 AM
Change the name M rto refer to

=OFFSET(M!$D$9,1,0,SUMPRODUCT(--(M!$D:$D<>""))-1,1)

also change the allied names in the same way.