Hi there,
I am trying to create a spreadsheet where for each entry (rows) I need to put some values (36 in total with numeric values from 1 to 50). For each entry I also have to choose a position (between 36 positions) and a duty (among 5 duties), now I have wrote a formula which returns a different number according to both the position and the duty that I've chosen for that entry. I need this because for every single combination of position and duty I need to "highlight" some important values. I have 66 possible combinations but for the nature of Excel I can only create a maximum of 64 nested level so I needed to divide the formula in two cells. This is the formula I've created, since I am Italian (so I apologize for my English skills) I have the Italian language so please replace SE with IF and E with AND:
As you can see what it does is checking which position and duty I had given to the entry in the row 3 (D3 position and AO3 duty) and then it returns a number according to the combination created. This formula is really basic so I think that it could be replaced by a cleaner and much easier formula. The problem with it is that if I copy the formula in a different cell, all the "cell references" change while I need that CC3to37 and CE3to7 don't change.
Is there anybody who can help me?
Thanks,
I am trying to create a spreadsheet where for each entry (rows) I need to put some values (36 in total with numeric values from 1 to 50). For each entry I also have to choose a position (between 36 positions) and a duty (among 5 duties), now I have wrote a formula which returns a different number according to both the position and the duty that I've chosen for that entry. I need this because for every single combination of position and duty I need to "highlight" some important values. I have 66 possible combinations but for the nature of Excel I can only create a maximum of 64 nested level so I needed to divide the formula in two cells. This is the formula I've created, since I am Italian (so I apologize for my English skills) I have the Italian language so please replace SE with IF and E with AND:
Code:
=SE(E(D3=CC3,AO3=CE5),1,SE(E(D3=CC4,AO3=CE5),2,SE(E(D3=CC4,AO3=CE6),3,SE(E(D3=CC4,AO3=CE7),4,SE(E(D3=CC5,AO3=CE5),5,SE(E(D3=CC6,AO3=CE6),6,SE(E(D3=CC6,AO3=CE7),7,SE(E(D3=CC7,AO3=CE5),8,SE(E(D3=CC7,AO3=CE4),9,SE(E(D3=CC7,AO3=CE3),10,SE(E(D3=CC8,AO3=CE5),11,SE(E(D3=CC8,AO3=CE4),12,SE(E(D3=CC8,AO3=CE3),13,SE(E(D3=CC9,AO3=CE5),14,SE(E(D3=CC9,AO3=CE4),15,SE(E(D3=CC9,AO3=CE3),16,SE(E(D3=CC10,AO3=CE5),17,SE(E(D3=CC10,AO3=CE6),18,SE(E(D3=CC10,AO3=CE7),19,SE(E(D3=CC11,AO3=CE5),20,SE(E(D3=CC12,AO3=CE5),21,SE(E(D3=CC12,AO3=CE6),22,SE(E(D3=CC12,AO3=CE7),23,SE(E(D3=CC13,AO3=CE7),24,SE(E(D3=CC14,AO3=CE5),25,SE(E(D3=CC14,AO3=CE6),26,SE(E(D3=CC15,AO3=CE5),27,SE(E(D3=CC15,AO3=CE6),28,SE(E(D3=CC16,AO3=CE5),29,SE(E(D3=CC16,AO3=CE6),30,SE(E(D3=CC17,AO3=CE5),31,SE(E(D3=CC18,AO3=CE5),32,SE(E(D3=CC19,AO3=CE6),33,SE(E(D3=CC20,AO3=CE5),34,SE(E(D3=CC20,AO3=CE6),35,SE(E(D3=CC20,AO3=CE7),36,SE(E(D3=CC21,AO3=CE6),37,SE(E(D3=CC22,AO3=CE6),38,SE(E(D3=CC22,AO3=CE7),39,SE(E(D3=CC23,AO3=CE5),40,SE(E(D3=CC23,AO3=CE6),41,SE(E(D3=CC24,AO3=CE5),42,SE(E(D3=CC24,AO3=CE6),43,SE(E(D3=CC24,AO3=CE7),44,SE(E(D3=CC25,AO3=CE6),45,SE(E(D3=CC25,AO3=CE7),46,SE(E(D3=CC26,AO3=CE6),47,SE(E(D3=CC26,AO3=CE7),48,SE(E(D3=CC27,AO3=CE7),49,SE(E(D3=CC28,AO3=CE7),50,SE(E(D3=CC29,AO3=CE7),51,SE(E(D3=CC30,AO3=CE6),52,SE(E(D3=CC30,AO3=CE7),53,SE(E(D3=CC31,AO3=CE6),54,SE(E(D3=CC31,AO3=CE7),55,SE(E(D3=CC32,AO3=CE6),56,SE(E(D3=CC32,AO3=CE7),57,SE(E(D3=CC33,AO3=CE7),58,SE(E(D3=CC34,AO3=CE6),59,SE(E(D3=CC34,AO3=CE7),60,SE(E(D3=CC35,AO3=CE7),61,SE(E(D3=CC36,AO3=CE6),62,SE(E(D3=CC36,AO3=CE7),63,SE(E(D3=CC37,AO3=CE5),64,0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))As you can see what it does is checking which position and duty I had given to the entry in the row 3 (D3 position and AO3 duty) and then it returns a number according to the combination created. This formula is really basic so I think that it could be replaced by a cleaner and much easier formula. The problem with it is that if I copy the formula in a different cell, all the "cell references" change while I need that CC3to37 and CE3to7 don't change.
Is there anybody who can help me?
Thanks,
