Add colour drop down list to VLOOKUP cell

[milo1234]

Hi Team, 

I would like to add a dropdown list of colours (green, amber, red) however no text to cell F4-F7 which uses a VLOOUKP. 

For example, the 'Status Delivery' stays the same, however I would like to add a colour to this cell. 

Please can someone help? 

Thank you

[#2]

Hi Milo,

Can you provide more information as to when you will apply red, amber, green? It is not clear from the dates which colour you want to apply?

let me know and I will see what can be done.

thanks

Peter

[#3]

Hi Peter,

Thank you for your response.
So I would like to give the user an option to colour the 'Status Delivery' date column.

For example, F4 should be the colour green as my team has nearly completed this status by the delivery date, however F5 should be red as the team has not completed the status by the delivery date.

I want the drop down list to be the 3 colours so they can easily colour the cell, rather than having to apply the colour from the toolbar each time. However please note, when colour coding the cells, the date should always be visible.

I hope this makes sense. Sorry if I am not being clear.

[#4]

Hi Milo,

Sorry - I am missing something here.

F4 and F5 look the same to me? How would I know that your team has nearly completed this status by the delivery date? The status delivery is the same in both cells?

Sorry , can you clarify further?

thanks

Peter

[#5]

Hi @peteryac60 

Apologies, I am not explaining this well.

All I need is a drop down list on column F to show the colours red, amber, and green which my team will select on their own.

Dependent on the colour my team choose from the drop-down list, it will colour the cell in column F but keeping the VLOOKUP of the date at all times.

I have updated the attachment to show what I need but obviously the colours will show in a drop-down list. 

Thanks,

[#6]

@milo1234 

Hi Milo

I am not aware of any way to apply colours using VLOOKUP.

A better option might be using conditional formatting. I have attached an example - the colour will change depending on the colour choosing in the new column F.  I am not sure if this will meet your needs? 

Of course , you can move the colour column out of the main table if that is preferable?

Hopefully this will give you some food for thought?

regards,

Peter

[#7]

Hi Peter,

Unfortunately this won't work, but thank you.
I am trying to allow a dropdown list of 3 colours for the user to choose from.

Thanks for your help anyways.

[#8]

@milo1234 

Hi Milo,

I am still struggling to understand what you are trying to achieve. :)

In column F you have the vlookup which works fine and populates. I think what you want is an ability to colour those cells. Is that correct?

But do you want this colouring to be done manually by a user? Or do you want a value in the table to 'trigger' the change?

Does my question make sense?

Peter

[#9]

Hi Peter,

I want the vlookup to also have a dropdown to allow the user to colour to cell either green, amber or red.

The user will manually select the colour from the dropdown. The vlookup result will NOT change.

[#10]

Hi Milo,
I think what you want is a vlookup to select the status - which you already have , and that works fine - and a second vlookup to select the colour. I'm afraid it is not possible to have two vlookups in the same cell.

What I am struggling to understand is what determines the colour to be applied? It is not clear to me from the data you provided.

I still think the way to do this is conditional formatting based on certain conditions. I know you did not like the proposal I made before but you can adjust this to apply the formatting by checking (for example) the dates. So if a WP was due to be delivered by Jul-21 but was actually delivered by Aug-21 you could set the conditional format to RED. If on the other hand, it is delivered by Jul-21 then the format would be GREEN. And so on. Does that make sense?

Or are you looking for the user to manually set the colour using some criteria which are not held withing the spreadsheet? If that is the case you would need to resort back to the tool bar....

Unless you write a VBA macro to control this - but in my view that may be a sledgehammer to crack a nut approach!

Hope this helps.

Peter