I'm trying to create a spreadsheet to track my regular expenses (insurance, strata, power, etc) and I want the box to show a clear and easy to tell colour showing me its status (Green for paid, red for unpaid and possibly a blue or something for automatic payments). So i can just glance at the sheet and know instantly what ones I need pay and when their due.
I've managed to create a drop down list that doesn't retain its colour and a drop down list that retains its colour but I can't get a drop down list for multiple cells that retain their colour and I'm hoping someone can walk me through a simple way to do this.
DROP DOWN LIST NO COLOUR.
Method.
1) Create second sheet.
2) Create list Paid (green), Unpaid (Red), Automatic (Teal) on second sheet.
3) Sheet 1 select cell (E.G E3) and "Data", "Validity", then select "Allow - Cell Range" and for source select the cells on sheet 2.
Result: A drop down list that allows me to select Paid, Unpaid or Automatic which I can just copy and paste for multiple cells.
Problem: Colour formatiing isn't retained so its all white.
DROP DOWN LIST COLOUR
Method
1) As above to step 2.
2) Click on "Paid" then "Styles" then "New Style" and type in Paid.
3) Repeat step 2 only substitute Unpaid for Paid.
4) Repeat step 2 only substitute Automatic for Paid.
5) Next to cell containing "Paid" insert code "=IF(E3="Paid";1;0)".
6) Next to cell containing "Unpaid" insert code =IF(E3="Unpaid";1;0).
7) Next to cell containing "Automatic" insert code =IF(E3="Automatic";1;0)
8) Repeat step 3 from first method.
Result: A drop down list that allows me to select Paid, Unpaid or Automatic with appropriate colouring.
Problem: I can only make a new cell that allows this by creating a seperate set of coded 1,0 conditions which means I'm filling 3 new cells in sheet 2 for each entry. I could do this but there has to be a simpler way. I know in office at work I can just pick "Conditional formating, highlight rules, equal to" but office doesn't have that option as far as I can tell and equal too isn't accepted unless its a number.