Hussain_0929
New Member
Greetings
My requirements are:
formula cell is G16
I'm trying to assign auto numbering according to discipline cell(H16) and building cell(J16) criteria. It is working but exceeding 64 nesting. Need to shorten this formula. Need support. I attached the sheet
My requirements are:
formula cell is G16
I'm trying to assign auto numbering according to discipline cell(H16) and building cell(J16) criteria. It is working but exceeding 64 nesting. Need to shorten this formula. Need support. I attached the sheet
=IF(H16="","",IF(AND(J16="1BS02",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BS02"),IF(AND(J16="1BS03",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BS03"),IF(AND(J16="1BS04",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BS04"),IF(AND(J16="1BS02",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BS02"),IF(AND(J16="1BS03",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BS03"),IF(AND(J16="1BS04",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BS04"),IF(AND(J16="1BS02",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BS02"),IF(AND(J16="1BS03",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BS03"),IF(AND(J16="1BS04",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BS04"),IF(AND(J16="1BS02",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BS02"),IF(AND(J16="1BS03",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BS03"),IF(AND(J16="1BS04",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BS04"),IF(AND(J16="1BS02",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BS02"),IF(AND(J16="1BS03",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BS03"),IF(AND(J16="1BS04",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BS04"),IF(AND(J16="1BS02",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BS02"),IF(AND(J16="1BS03",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BS03"),IF(AND(J16="1BS04",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BS04"),IF(AND(J16="1BN02",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BN02"),IF(AND(J16="1BN03",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BN03"),IF(AND(J16="1BN04",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BN04"),IF(AND(J16="1BN02",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BN02"),IF(AND(J16="1BN03",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BN03"),IF(AND(J16="1BN04",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BN04"),IF(AND(J16="1BN02",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BN02"),IF(AND(J16="1BN03",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BN03"),IF(AND(J16="1BN04",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BN04"),IF(AND(J16="1BN02",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BN02"),IF(AND(J16="1BN03",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BN03"),IF(AND(J16="1BN04",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BN04"),IF(AND(J16="1BN02",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BN02"),IF(AND(J16="1BN03",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BN03"),IF(AND(J16="1BN04",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BN04"),IF(AND(J16="1BN02",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BN02"),IF(AND(J16="1BN03",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BN03"),IF(AND(J16="1BN04",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BN04"),IF(AND(J16="BRT",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"BRT"))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Attachments

30 KB Views: 2