PDA

View Full Version : Solved: Nested IFs



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

mbarron
02-14-2011, 10:06 PM
Try this formula:
=INDIRECT(IF(G2=1,"","Datasheet!"& INDEX({"$c11:","$c18:","$c25:","$c32:","$c37:","$c45:"},G2-1)&INDEX({"$c13","$c20","$c27","$c333","$c41","$c46"},G2-1)

lacviet2005
02-14-2011, 10:31 PM
=INDIRECT(If(G2=1,"","Datasheet!"& INDEX({"$c11:","$c18:","$c25:","$c32:","$c37:","$c45:"},G2-1)&INDEX({"$c13","$c20","$c27","$c33","$c41","$c46"},G2-1)))


Thanks mbarron for your quick response, but I got this message: "You may not use unions, intersections, or array constants for Data Validation criteria."

Any ideas?

Regards,
LV

mikerickson
02-14-2011, 10:39 PM
=CHOOSE(DataSheet!G2, "", DataSheet!$C$11:$C$13, DataSheet!$C$18:$C$20, DataSheet!$C$25:$C$27, DataSheet!$C$32:$C$33, DataSheet!$C$37:$C$41, DataSheet!$C$45:$C$47)

I noticed that DataSheet!$C$32:$C$33 is only two rows while the rest are three.

lacviet2005
02-14-2011, 10:52 PM
Thanks Mike!!

I really appreciate for your time and effort.

Cheers!