Using Drop Down menus for data entry

[Sambrowne]

I'm trying to create a method of tracking the use of hours across my business over and above standard hours. There are three data fields; over hours, overtime and agency. I've created a dropdown menu which gives those three options but what I'd like to happen is that when they select Agency (for example) I'd like the cell to go empty and allow them to enter a numerical value, let's say 10 for Mon on HCF. Additionally, as it's agency I'd like the figure 10 to run red and automatically populate M11 in the same colour. Totals I can sum easily enough. Thanks!

 

[awoohaw]

DV (Data validation) doesn't work that way. In "List" validation the selected item from the drop down is what goes into the cell.

There are other forms of DV that doesn't use drop downs. But as a test in your scenario i tried this formula:
=OR(E11="Hours Over", E11="Over Time",E11>0)
the formula was accepted in the DV dialog, but any text was allowed, although it would not allow numbers less than or equal to zero.

 

[Sambrowne]

Thanks, I had a feeling that DV wouldn't work the way I was trying to work it. I couldn't see where you entered the text though, did you go to Custom?

 

[awoohaw]

Thanks, I had a feeling that DV wouldn't work the way I was trying to work it. I couldn't see where you entered the text though, did you go to Custom?

Yes, from the "allow" option drop down, select "Custom".

 

[Sambrowne]

Hmm, I tried it but nothing seemed to happen. Is there another way of doing this do you think?

 

[awoohaw]

well. I"m not sure what you tried. When I tried using the "custom" formula it still did not work for me.
I think you need to use some other way of tracking, maybe a helper column.

if the "tracker" format in your image is what you must keep, then I suggest having a different worksheet with only 4 columns:
Serv, Dept, Type, & Date. Then provide a summary by building a pivot table or a formula grid that looks like your image.

 

[Sambrowne]

Cheers, I’ll probably have to rethink this.

 

[awoohaw]

Cheers, I’ll probably have to rethink this.

For what it is worth. I think most advanced excel users want to keep the RAW data separate from any reporting or analysis worksheets.
So, having a separate worksheet to keep your input is not unusual.

Best Wishes!

 

[Sambrowne]

Yeah, there’s the thing. As an unadvanced user I’m struggling with it a bit!