Conditional Formatting a Table

[Pkrutilek1550]

I am new to this so bare with me. 

I am creating a schedule for 5 people. There are two columns and 8 rows. The column on the left has a dropdown menu with options 1-8 representing hours. There is also another cell containing a dropdown menu with the names of the 5 people who will be scheduling. The goals are as follows:

 A row will automatically shade as the number of hours are choses. For example, if in the first row "1 hour" is chosen by "Dale", the row will be shaded the color assigned to Dale. If 2 hours, 2 rows, etc. After 8 rows is shaded, the entire table is full. 

currently, the rows are correctly shading with the selected number of hours and in the in the correct colors. 

Here are the problems:

1. When another name is chosen from the drop-down menu, the rows that are already shaded automatically change to the color of the new user. They need to stay the original color and be locked for changes. 

2. Number of hours can manually be entered ending up at more than 8 hours per day. Is there a way to limit input to the drop down only, no manual entry?

3. When I go back in to conditional formatting, the formulas are different. Is there something I am doing wrong? A way to prevent this from happening?

Conditional Formatting rules entered in each row for each user. 

Row 1    =AND($A$2="DAVE", C5>=1) ....

Row 2    =AND($A$2="DAVE", C5+C6>=2) ....

Row 3    =AND($A$2="DAVE", C5+C6+C7>=3) ....

Any help is appreciated.

[#2 · Accepted solution]

@Pkrutilek1550 

Hello,

Answering your questions:

1) Based on your explanation, you have only one cell with a drop-down list for the names of the people. Logically, if you change that cell value (i.e. select another name for a new entry), all conditional formatting formulas will understand that and then change all accordingly. My suggestion is to include a drop-down for each row, and then assign a formula in accordance and in reference to that specific cell in the row. That is, for row 1 you have a drop-down list for a name entry, then you formulate your conditional formatting refering to that drop-down located in row 1. The same for row 2, 3, etc. Is that convenient for you? If that is not the case, you would be left to use VBA code for your desired functionality.

2) To prevent users entering manual values in a drop-down list you can go to Data tab, Data Validation, Error Alert and finally "Show error alert after invalid data is entered". If you want users to enter manually a number without a list, you can also go to Data Validation, Allow Whole Number and then set a minimum or a maximum (8).

3) It is difficult to know what is happening without having a sample document, for instance. But if you like my suggestion regarding point 1), you could use something like =A2="Dave". That would be all for your desired output. Conditional formatting will understand that if in cell A2 there is Dave, then you can fill with any color you like. Add additional conditions as desired with AND(), as you have already made.

Hope it helps.

Martin

[#3]

Very helpful. Thank you!