[Solved] Colour Changing Lists

[MythicBooster]

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 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

[FJCC]

You should set the Conditional Format formula in cell C6 to

Code: Select all

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.

[MythicBooster]

thanks really apreciate it

[mikele]

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

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.

[MythicBooster]

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

[FJCC]

@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

[MythicBooster]

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.

[mikele]

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.

[MythicBooster]

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??

[FJCC]

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.

[mikele]

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

STYLE(VLOOKUP($C5;Tbl;2;0))

[MythicBooster]

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.

[MythicBooster]

@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