Please view attached workbook.
I am looking to put a formula in range B2:B3 on Employee worksheet.
Column A - Is a list of employees. (It has a named range of "Active")
Column B - Is where I am needing a formula (I'll explain this further below)
Is a log of daily sales.
Column A - Contains the date. (It has a named range of "Date")
Column B - Contains the employee name. (It has a named range of "NamesInLog")
Column C - Contains their sales.
Here is my problem...
As you can see, there are only two active employees.
On the Log worksheet, the two employees both logged their sales on 01-01-15. They had picked their name from a drop down list in column B. That drop down list, is looking at named range Active.
In my real workbook, it will be looking at named range Available instead.
I would like the Available list, to only show the employee name IF they don't already have sales listed for that date.
So for example...
If you look at row 4, John Smith already has his sales logged for the day. On row 5, the date is still 01-02-15, so if John Smith has ALREADY logged his sales for the day, there's only one more employee left that can log their sales - which is Karen Smith.
So basically, with the formula you create - your end result in range B2:B3 on Employee worksheet should be: Karen Smith only.
Thank you in advance for your help!