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
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