I use Excel 2007. I have a spreadsheet of sales. Each salesman has three
rows assigned: sales, payments and commissions. These are listed under the
months of the trade year, April to March as a heading row. I don't want to
pay monthly commission until the salesman has submitted the payments for the
particular month. I have a separate section showing what payments are due.
So what I want is to change the colour of the box in the separate section, so
that if the payment has not been received in a particular month, the box is
one colour, if the payment is not received it is a different colour. That
way I will be alerted not to pay commission if no payment has been received.
But it is more complicated than that, as I want it to update automatically.
So for example cell S23 would have to have something like: in the row C2 to
N2, take the cell that is in the column for the current month and if that
cell 0, color S23 green; if not, color S23 red.
Is this possible with conditional formatting? If not, is there a way I
could do it?
I have several salesman to do this for.