[Solved] Create a drop down list with coloured box

Discuss the spreadsheet application

[Solved] Create a drop down list with coloured box

Postby Senko » Tue Jul 16, 2019 9:55 am

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.
Last edited by Hagar Delest on Wed Jul 17, 2019 7:58 am, edited 1 time in total.
Reason: tagged solved
Windows 10 Open Office 6.0.4.2
Senko
 
Posts: 2
Joined: Tue Jul 16, 2019 9:35 am

Re: How do I create a drop down list with coloured box.

Postby F3K Total » Tue Jul 16, 2019 6:05 pm

Hi,
find attached an example using Menue Data/Validity/from List, to create a dropdown, to select paid, unpaid or automatic and Menue Format/Conditional Formatting using three cellstyles, i called green, red and blue, having according backgroundcolors.
R
Attachments
paid_unpaid_conditional_formatting.ods
(12.59 KiB) Downloaded 12 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 914
Joined: Fri Dec 16, 2011 8:20 pm

Re: How do I create a drop down list with coloured box.

Postby Senko » Wed Jul 17, 2019 4:26 am

Thank you this is just what I was after.
Windows 10 Open Office 6.0.4.2
Senko
 
Posts: 2
Joined: Tue Jul 16, 2019 9:35 am


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 19 guests