[Solved] Colour Changing Lists

Discuss the spreadsheet application

[Solved] Colour Changing Lists

Postby MythicBooster » Mon Dec 30, 2019 7:43 pm

Hi all,

I have been working on this for a couple of days now and not making headway. I have included the document I am working with in the attachments.

To start with I have been trying to work through this using this previous topic https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=26183

The dead section that highlights the row if this is such I am also happy with.

What I am trying to do is have a drop down list for locations (leads and exit) from a generated list (Housed in the ALL sheet). When I select a Darkworld location from the drop down list generated from selection in ALL I would like the box to turn purple to define it as a Darkworld location and thus making it more obvious. Using the above topic refered to no mater what I have tried I cant seem to get this to work. I feel I may be close pos somthing to do with the Tbl;2;0)) section at the end of the conditional formating statement STYLE(VLOOKUP($A1&"*";Tbl;2;0))

The other thing I am trying to do is have it so once a particular destination is picked from the list it is not possible to pic it again (either it is removed, or there is a warning that it has been picked)

Thanks so much in advance for any help given, because this is melting my brain.

THANIKS James
Attachments
Entrance Rando Spreadsheet.ods
This is the full file I am working with
(19.45 KiB) Downloaded 17 times
Last edited by Hagar Delest on Sat Jan 04, 2020 10:33 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.7 - Windows 10 Home
MythicBooster
 
Posts: 7
Joined: Mon Dec 30, 2019 7:23 pm

Re: Colour Changing Lists PLEASE HELP!

Postby FJCC » Mon Dec 30, 2019 8:20 pm

You should set the Conditional Format formula in cell C6 to
Code: Select all   Expand viewCollapse view
STYLE(VLOOKUP($C6&"*";Tbl;2;0))

You have it looking at the value of cell A1.
I do not know of a way to enforce that each value be unique. I will give it some thought.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Colour Changing Lists PLEASE HELP!

Postby MythicBooster » Mon Dec 30, 2019 8:44 pm

thanks really apreciate it
OpenOffice 4.1.7 - Windows 10 Home
MythicBooster
 
Posts: 7
Joined: Mon Dec 30, 2019 7:23 pm

Re: Colour Changing Lists PLEASE HELP!

Postby mikele » Mon Dec 30, 2019 8:49 pm

Hi,
I think you have to use conditional formatting. But you need 3 conditons (one for each colour) - this could be a problem with OpenOffice. You can use the extension MOTTCO (More Then Three COnditions).
The Conditional Format formula in cell C6:
Code: Select all   Expand viewCollapse view
VLOOKUP(C6;$ALL.$A$3:$C$128;3;0)="_DK"
(for _LT1, _LT2 the same way)
Your second question can be solved by to additional areas on the table ALL. I called them "leads_to" and "exits_to" and used them for validation in 'Entrance Checker'.C5:C17 and 'Entrance Checker'.D5:D17.
Attachments
Entrance Rando Spreadsheet.ods
(24.01 KiB) Downloaded 17 times
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 59
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Colour Changing Lists PLEASE HELP!

Postby MythicBooster » Mon Dec 30, 2019 9:52 pm

So have transfered the no repeat over and thats amazing thanks very much indeed, exactly what I wanted. As regards to the colour change for the dark world locations. In the formula I could use 3 colours, that would still work for me. If I send the new file I am working with would you mind taking another look for me please. THANKS
OpenOffice 4.1.7 - Windows 10 Home
MythicBooster
 
Posts: 7
Joined: Mon Dec 30, 2019 7:23 pm

Re: Colour Changing Lists PLEASE HELP!

Postby FJCC » Mon Dec 30, 2019 10:43 pm

@mikele - Nice solution for enforcing unique entries! Using the STYLE function in the conditional format formula takes care of the limitation of three conditions for conditional formatting. The VLOOKUP returns the style name and the STYLE function applies it
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Colour Changing Lists PLEASE HELP!

Postby MythicBooster » Mon Dec 30, 2019 11:13 pm

As regards to getting the colour change to work @FJCC think I am still confused even trying to get it to work for just the colour change to purple for the one colour change can't get it to work. Would it be possible to explain how to do this in a more simple way please. THANKS again everyone for all you input.
OpenOffice 4.1.7 - Windows 10 Home
MythicBooster
 
Posts: 7
Joined: Mon Dec 30, 2019 7:23 pm

Re: Colour Changing Lists PLEASE HELP!

Postby mikele » Tue Dec 31, 2019 12:07 am

Hi,
@FJCC: it's interesting that a condition (here a formula) can change the content of the cell - but it works.
The "*" is not so helpful, because it is also used as a "wildcard" in regulare expressions which used in calc function by standard.
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 59
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Colour Changing Lists PLEASE HELP!

Postby MythicBooster » Tue Dec 31, 2019 12:12 am

I'm looking into somthing now which is creating a table of darkworld locations and naming it darkworld can I just do some conditional formating based on the full location names in each line? If so how would I do it like this??
OpenOffice 4.1.7 - Windows 10 Home
MythicBooster
 
Posts: 7
Joined: Mon Dec 30, 2019 7:23 pm

Re: Colour Changing Lists PLEASE HELP!

Postby FJCC » Tue Dec 31, 2019 2:41 am

I think I have column C on the Entnrance checker tab working as you would like. Notice that I set the extra character to be # instead of * ( see column B of the All Sheet) because, as mikele pointed out, the * interferes with using regular expressions. That may be why you are having trouble.
Attachments
Entrance Rando Spreadsheet.ods
(24.94 KiB) Downloaded 14 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Colour Changing Lists PLEASE HELP!

Postby mikele » Tue Dec 31, 2019 1:38 pm

Hi,
I think there is no need for this special column "Lookup Value" on ALL. You can remove it and let the named area Tbl be $ALL.$A$2:$B$128. Then the Conditional Format formula is a little bit easier
Code: Select all   Expand viewCollapse view
STYLE(VLOOKUP($C5;Tbl;2;0))
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 59
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Colour Changing Lists PLEASE HELP!

Postby MythicBooster » Thu Jan 02, 2020 7:42 am

Thank you for all of this input everyone. I am not at home till Friday now so will look at these bits you have suggested then and report back. Thanks again.
OpenOffice 4.1.7 - Windows 10 Home
MythicBooster
 
Posts: 7
Joined: Mon Dec 30, 2019 7:23 pm

Re: Colour Changing Lists PLEASE HELP!

Postby MythicBooster » Fri Jan 03, 2020 4:53 pm

@mikele This is AMAZING and works SPOT on, thank you so very much. I have some more ideas that I would like to implement bit more wish list ish.

The first thing is not a wish list item. So once I have selected a loction that has a different colour selection, if I choose to delete it again the box deoes not go back to the original colour. Is there a way to do this??

The wish list thing is, is it posibble to have have it when I hover a box or select a box I can make it display an image relating to that selection?

The final is a search function so I can search and it will then show those locations in the form/database.

Thanks all your awesome
OpenOffice 4.1.7 - Windows 10 Home
MythicBooster
 
Posts: 7
Joined: Mon Dec 30, 2019 7:23 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests