Excel Data Validation List

[Beatrix]

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.Attachment 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]

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]

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]

Hi James ,

I tried to attach the workbook again..

Cheers,
Yeliz

[Beatrix]

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]

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.