lacviet2005
02-14-2011, 05:08 PM
Dear all xcel gurus,
I have a long formula for a data validation cell:
=INDIRECT(IF(DataSheet!G2=1,"",(DataSheet!G2=2,("DataSheet!$C$11:$C$13"),IF(DataSheet!G2=3,("DataSheet!$C$18:$C$20"),IF(DataSheet!G2=4,("DataSheet!$C$25:$C$27"),IF(DataSheet!G2=5,("DataSheet!$C$32:$C$33"),IF(DataSheet!G2=6,("DataSheet!$C$37:$C$41"),IF(DataSheet!G2=7,("DataSheet!$C$45:$C$46")))))))))
Because its length was too long so i followed the tutorial on this website (http://www.cpearson.com/excel/nested.htm)
So, i defined the Criteria1 with: =IF(DataSheet!$G$2=1,"",IF(DataSheet!$G$2=2,("Datasheet!$C$11:$C$13"),IF(DataSheet!$G$2=3,("Datasheet!$C$18:$C$20"),IF(DataSheet!$G$2=4,("Datasheet!$c$25:$c$27"),FALSE)))))
and Criteria2 with:
=IF(DataSheet!$G$2=5,("Datasheet!$C$32:$C$33"),IF(DataSheet!$G$2=6,("Datasheet!$C$37:$C$41"),IF(DataSheet!$G$2=7,("Datasheet!$C$45:$C$46"))))
And, in the data validation cell, i have "list" selected and the source field entered as:
=INDIRECT(IF(Criteria1,Criteria1,Criteria2))
It seemed to work for Criteria2, but not the other one.
Your help would be greatly appreciated! : pray2:
Regards,
LV
I have a long formula for a data validation cell:
=INDIRECT(IF(DataSheet!G2=1,"",(DataSheet!G2=2,("DataSheet!$C$11:$C$13"),IF(DataSheet!G2=3,("DataSheet!$C$18:$C$20"),IF(DataSheet!G2=4,("DataSheet!$C$25:$C$27"),IF(DataSheet!G2=5,("DataSheet!$C$32:$C$33"),IF(DataSheet!G2=6,("DataSheet!$C$37:$C$41"),IF(DataSheet!G2=7,("DataSheet!$C$45:$C$46")))))))))
Because its length was too long so i followed the tutorial on this website (http://www.cpearson.com/excel/nested.htm)
So, i defined the Criteria1 with: =IF(DataSheet!$G$2=1,"",IF(DataSheet!$G$2=2,("Datasheet!$C$11:$C$13"),IF(DataSheet!$G$2=3,("Datasheet!$C$18:$C$20"),IF(DataSheet!$G$2=4,("Datasheet!$c$25:$c$27"),FALSE)))))
and Criteria2 with:
=IF(DataSheet!$G$2=5,("Datasheet!$C$32:$C$33"),IF(DataSheet!$G$2=6,("Datasheet!$C$37:$C$41"),IF(DataSheet!$G$2=7,("Datasheet!$C$45:$C$46"))))
And, in the data validation cell, i have "list" selected and the source field entered as:
=INDIRECT(IF(Criteria1,Criteria1,Criteria2))
It seemed to work for Criteria2, but not the other one.
Your help would be greatly appreciated! : pray2:
Regards,
LV