HI all,
I have about fifty rows that I have to reference in a formalau. I have done the first 4:
=IF($AA$6="NAME 1",ROUNDUP(INDEX($A$5:$Y$56,1,4)*$F$3*(1+$I$62*AB$7)/($D$3*$E$3*$G$3*$H$3),0),IF($AA$6="NAME 2",ROUNDUP(INDEX($A$5:$Y$56,2,4)*$F$3*(1+$I$62*AB$7)/($D$3*$E$3*$G$3*$H$3),0),IF($AA$6="NAME 3",ROUNDUP(INDEX($A$5:$Y$56,3,4)*$F$3*(1+$I$62*AB$7)/($D$3*$E$3*$G$3*$H$3),0),IF($AA$6="NAME 4",ROUNDUP(INDEX($A$5:$Y$56,4,4)*$F$3*(1+$I$62*AB$7)/($D$3*$E$3*$G$3*$H$3),0)))))
SO if AA6 equals NAME 1, I pick up the right number via the "INDEX($A$5:$Y$56,1,4)" part of the formula. My formula works. BUt there are 50 Names I have to reference and each name will chane the row reference in the INDEX part of the formula by 1.
Is there a shorter way of doing this?
Thanks for any help in advance.
I have about fifty rows that I have to reference in a formalau. I have done the first 4:
=IF($AA$6="NAME 1",ROUNDUP(INDEX($A$5:$Y$56,1,4)*$F$3*(1+$I$62*AB$7)/($D$3*$E$3*$G$3*$H$3),0),IF($AA$6="NAME 2",ROUNDUP(INDEX($A$5:$Y$56,2,4)*$F$3*(1+$I$62*AB$7)/($D$3*$E$3*$G$3*$H$3),0),IF($AA$6="NAME 3",ROUNDUP(INDEX($A$5:$Y$56,3,4)*$F$3*(1+$I$62*AB$7)/($D$3*$E$3*$G$3*$H$3),0),IF($AA$6="NAME 4",ROUNDUP(INDEX($A$5:$Y$56,4,4)*$F$3*(1+$I$62*AB$7)/($D$3*$E$3*$G$3*$H$3),0)))))
SO if AA6 equals NAME 1, I pick up the right number via the "INDEX($A$5:$Y$56,1,4)" part of the formula. My formula works. BUt there are 50 Names I have to reference and each name will chane the row reference in the INDEX part of the formula by 1.
Is there a shorter way of doing this?
Thanks for any help in advance.