LinkBack URL
About LinkBacks
Hi All,
I have a workbook ("attached") that has a name column on (4) worksheets. They are identified as (1st Qtr, 2nd Qtr, 3rd Qtr, and 4th Qtr) respectively. The data validation pulls from a list on the worksheet titled (DATA). For some reason, I continually lose this validation. There are two solutions I am seeking.
1st Solution: To have data validation enabled on the worksheets mentioned above (1st thu 4th Qtrs) in the "Employee Name" columns. The "validation list" is located on the ("DATA") worksheet, and the list itself is titled (Union Reps).
2nd Solution: At times we lose or gain a Union Rep. Therefore, I need to create a validation that is easy to manipulate. After this workbook is completed, it will be distributed to others across the country that haven't the expertise to interpret such things as validation and how it works. So I was hoping to create a list that you could simply click (Add Union Rep) or (Delete Union Rep), and the validation drop down would recognize the newer list, without having to manually revise the list on the ("DATA") worksheet.
3rd Solution: You will notice on the bottom of worksheets (1st Qtr through 4th Qtr), that I have a "SumIf" formula that is looking at the name of the employee (aka....Union Rep) to determine how many hours were utilized for the column heading it is placed under. As example, you will see on bottom of the worksheet (1st Qtr), the first name is Alfred Fonseca. To the right of this name you will see a Sumif for all corresponding columns. What I am needing on this, is that now we are looking at (2) areas of consideration. First you have the drop down validation in the "Employee Name" column on the top of the worksheet. This is where you enter their hours to the right of their names. Then you have the names below this area that have the sumif formulas to the right. So when the validation changes, (i.e., an employee has been added or deleted), this section needs to be equivalent to what you see at the top of the worksheet. To add, if you look at the worksheet ("Rep Hours"), you will see that the formulas are looking for the name (from) the corresponding worksheet. Overall there are (5) worksheets containing the names of these employees, which are (1st thru 4th Qtr worksheets + Rep Hours worksheet). So every name on the 1st thru 4th qtr, and the Rep Hours worksheet, need to be consistent.
If you can make any sense of this, I certainly look forward to any input you folks may have for this lost soul. Any questions, feel free in asking. I will respond fairly rapidly, as I am off today and Monday (10-13-14). Otherwise, I will catch you in the evenings after work. Many thanks!!
**Note: All PII (Personally Identifiable Information) has been removed. The names are fictitious.
Register To Reply
The workbook itself is somewhat simple in design, however, on the back end there are so many intricate details that require consideration. As stated to TMS, I am designing this to be used by "many" others in the agency, so when they get it, they have two major tasks to be considered. First is to go to the proper quarterly worksheet, enter the employee name (via validation), and then when their region adds or deletes an employee, they need to have a (simplistic) approach to doing so. When they execute that particular task, they should only have to go to one area to do so, rather than having to go to all sheets to enter changes manually. Thanks for your input however, and thank you for your assistance as well. 
Bookmarks