Help needed for a newbie


New member
Oct 24, 2013
Reaction score
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?

You need to just place a $ in front of the row and/or column reference in the cell reference.

e.g. =$CC3 will "freeze" the column
=CC$3 will "freeze" the row
=$CC$3 will "freeze" the column and row.
thanks a lot! I've found another solution but I have a new question :nerd:

now I need: if C2 = 1 then cells H2, M2, N2 and P2 must have bold and red font and green background, if C2 = 2 then H2, J2, R2 and Z2 must have bold and red font and green background and so on for values from 1 to 66. Is that only possible via the Conditional Formatting tool?