Solved: Nested IFs

[lacviet2005]

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

So, i defined the Criteria1 with: =IF(DataSheet!$G$2=1,"",IF(DataSheet!$G$2=2,("Datasheet!$C$11:$C$13"),IF(Da taSheet!$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,("Datash eet!$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!

Regards,
LV

[mbarron]

Try this formula:
[vba]=INDIRECT(IF(G2=1,"","Datasheet!"& INDEX({"$c11:","$c18:","$c25:","$c32:","$c37:","$c45:"},G2-1)&INDEX({"$c13","$c20","$c27","$c333","$c41","$c46"},G2-1)[/vba]

[lacviet2005]

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

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

Thanks Mike!!

I really appreciate for your time and effort.

Cheers!