Thread: how to create a counter based on an IF condition in Excel 2003

Hello,

Hopefully someone can shed some light on this problem I have:

If the value in cell A1 is 1, I would like cells B1 to L1 (the next 11 cells) to also display the value 1 and also for the cells A1 to L1 to turn green.

On the other hand if the value in cell A1 is 0 or nothing, then cells B1 to L1 will either display 0 or nothing corresponding to what the value is in cell A1 (0 or nothing). In addition, I would like the cells A1 to L1 in this case turn red.

However, I need this done for each cell in a row, For example: for any cell in a worksheet row I enter 1 in, I want the next 11 cells to automatically display 1, and to have the other conditions I wrote above.

Also what is important is that though; for example: cell A1 and B1 must have the same conditions, If i was to entre the value 1 in cell A1 the next 11 cells must display 1, but now that cell B1 has value of 1, I dont want the next 11 cells from cell B1 to display 1. The solution must recognise which cell has 1 entered into it and only the next 11 cells must automatically display 1.

Is this possible to do in Excel 2003,

Any help on this subject would be greatly appreciated!

There are probably more eloquent solutions this what I would do.
Enter your value in A1.
Move to B1. type "=" into B1 then move cursor to A1. Press "F4" 3 times (F4 key found on top row of keyboard) "enter". This will change what is in B1 to "=$A1) see why soon.
Now copy (move cursor to bottom right corner of B1; cursor becomes "+" click and drag to L1.
All cells from B1 to L1 will show "=$A1". If you don't press F4 then the formula becomes relative and C1 says "=B1" instead of "=$A1)
If A1 contains blank or zero other cells will display zero. To force other cells to display blank copy this into B1:L1
=IF($A1="","",$A1)
To change colour of cells to reflect value use "conditional formatting". I can't recollect where this is in excel 2003 but not too hard to find.

Highlight all cells in row you want to format like this. Enter your condition ie "if cell value" "is less than" "1" then format your colour preference. Apply another condition "if cell value" "is equal to or more than", "1" format ...

Now copy all your cells (click and drag as above) from B1 to L1, and drag them down the page for as many rows as you like.

Good luck.