Using a dropdown to format cells, but retain entered values

[Kingsof82]

Hi,

I'm not sure if this is possible (especially within my capabilities), but here goes:

I'm creating a document for managers within my company to track staff supervisions. I want to be able to enter the date of the supervision, then use a dropdown list with the following options in order to change the format, depending on which of the following type of supervision was used, with the date remaining visible:

Probationary
Meeting
Observation
Appraisal

I hope this makes sense, any help gratefully received, Thanks.

 

[Trevor G]

Welcome to the forum.

You can use conditional formatting to meet the conditions. What range and what colours do you want and is it just the cell that contains each word or highlight the row etc.

 

[LegendaryAxe]

If you don't want to use VBA, you can try putting those values somewhere in the workbook and use conditional formatting to create the dropdown list. Press alt + DL in the first cell of the column you want to have the dropdown list in, then choose "List" in the pop-up window and highlight your values. Then use format painter on the cells below the one you just modified to get the same dropdown list for as far as down in the column as you want. I'm not sure what you mean by changing the format, but you can then use conditional formatting to change the formatting of the rows based on the value in the "type of supervision" column.

 

[Kingsof82]

Hi,

Thanks for your responses, I'm not sure I explained myself properly on my initial question. I was hoping I'd be able to set up dropdown as in the below:

Then instead of replacing the cell value with the validation option, the cell formatting would be altered, but the date would remain, for example:

Having spelled it out like this, I'm not sure if it's even possible, especially given the outdated version of excel currently installed on my work machine.

Thanks

 

[Trevor G]

my suggestion is to have extra columns inbetween your month year columns and you can use the drop down validation as well as the conditional formatting and the dates would remain in the cells and the conditional formatting would be applied. If you don't want to see the validation lists you could hide the extra columns and show them when you want to pick from your list.

This can be done manually or via vba depending on your needs/ users knowledge.

let us know if this is a possible option you want to explore and I'm sure we can look to produce something for you to explore/try.

 

[Kingsof82]

my suggestion is to have extra columns inbetween your month year columns and you can use the drop down validation as well as the conditional formatting and the dates would remain in the cells and the conditional formatting would be applied. If you don't want to see the validation lists you could hide the extra columns and show them when you want to pick from your list.

This can be done manually or via vba depending on your needs/ users knowledge.

let us know if this is a possible option you want to explore and I'm sure we can look to produce something for you to explore/try.

Thanks Trevor. I was hoping to avoid having to add additional columns but my end goal has now changed so I'll now need to be adding these anyway. I'll try to remember how to use VBA to initiate a prompt to enter the supervision type whenever users enter a date (Experience tells me that if left to their own devices, they'll definitely end up leaving this field blank more often than not)