asked on

Need Formula Help, will involve lookups with conditions

Hello Experts,

Please view attached workbook.

I am looking to put a formula in range B2:B3 on Employee worksheet.

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)

Log Worksheet:

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!
EE-Example.xlsx

Professor J

is this only two names all the time? or the list can be many names?

Simon

I don't know of any way to do that, but here's an alternative approach, using the existing list range and conditional formatting to highlight the duplicate entries.
EE-Example-ConditionalFormat-v1.xlsx

Geekamo

ASKER

@ ProfessorJimJam - There are multiple names in the real workbook.

@ SimonAdept - Thank you for the alternative approach.

I had been looking everywhere for a solution, I don't want to give up on this idea yet. And I stumbled upon this video which is pretty close to what I am looking to do. The end result is exactly what I want, but I want my code to be aware of dates too. We'll see. :)

https://www.youtube.com/watch?v=1t4yl7P7-9A

byundt

If you have a reasonable length validation list (I believe less than 960 characters long), you can set it with a Worksheet_SelectionChange event macro and a user-defined function.

As written, the macro requires a date in column A before it will update the validation list.

'This macro goes on code pane for worksheet Log
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range, SalesLog As Range, targ As Range
Dim s As String
Dim vDate As Variant
Set SalesLog = Range("A2")          'First cell in sales log with a date
Set SalesLog = Range(SalesLog, Cells(Rows.Count, SalesLog.Column).End(xlUp)).Resize(, 2)
Set targ = SalesLog.Columns(2)      'Watch these cells for selections
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
    For Each cel In targ.Cells
        vDate = cel.Offset(0, -1).Value
        If vDate <> "" And IsDate(vDate) Then
            With cel.Validation
                .Delete
                s = Available(cel.Offset(0, -1).Value, ThisWorkbook.Names("Active").RefersToRange, SalesLog)
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=s
            End With
        End If
    Next
End If
End Sub

Open in new window

'This function goes on regular module sheet
Function Available(Date_ As Date, Active As Range, SalesLog As Range) As Variant
Dim emp As Variant
Dim s As String
Dim i As Long, n As Long
For Each emp In Active.Value
    If Application.CountIfs(SalesLog.Columns(1), Date_, SalesLog.Columns(2), emp) = 0 Then
        s = s & ", " & emp
        n = n + 1
    End If
Next
If n > 0 Then Available = Mid(s, 3)
End Function

Open in new window

EE-ExampleQ28607759.xlsm

Geekamo

ASKER

@ byundt.

Thank you very much for taking the time to write the code above. The code appears to work beautifully.

I do have a few questions...

This code will be looking at 20,000 records/rows. Is that going to be a problem?

Are you able to revise your code, so that the drop-down list is in alphabetical order?

Why did you choose a VBA solution, instead of a formula?

Thanks!

byundt

Geekamo,
Alphabetizing the list can be done.

Of greater concern is the maximum possible length of the dropdown list. A different approach will be required if it might exceed 960 or so characters. Think hard about this issue and please advise.

I started out trying to write a formula, but decided that it would be much more difficult for you to maintain than VBA code. The fact that you are wanting to add additional constraints proves that that was a good decision on my part.

Brad

Geekamo

ASKER

Hi Brad!
On break so typing this quickly.

The drop down list could appear on 20k rows, but the list itself should never have more than 30 names at any given time.

Am I still in trouble?

byundt

How many characters might be in those 30 names? If they average less than 30 characters per name, then I think you will be OK.

byundt

Geekamo,
The easiest and most efficient way to alphabetize the list of names in the dropdown is to alphabetize the employee list in your named range Active. If you do that, the list of "available" employees built by the VBA code will automatically be in alphabetical order.

Brad

Geekamo

ASKER

Brad,

Do you mind if I send you my real workbook privately?

I can attempt to put your solution into the actual workbook (at another time) but I would like you to see the real workbook so you can see if there are any additional issues?

Let me know your thoughts.

Thanks!

Geekamo

ASKER

If yes, I can send it sometime tonight. (Long work day today) Just let me know where I should send it if you would like to take a look. Thanks!

byundt

My email address is in my EE member profile.

Geekamo

ASKER

Brad,

I sent you an email to your EE address.

Jason

byundt

Jason,
I haven't seen your email yet. The best address is my alum.mit.edu one.

Brad

Geekamo

ASKER

Ok, sending in just one moment.

byundt

Geekamo did not want to post the workbook on a public forum, lest it be linked back to his employer.

After seeing the actual workbook, I needed to make changes to my previously suggested code. Issues included:
1. Different locations for the data being tested
2. Employee names contained embedded commas. As a result, I could not return the data validation formula as a string due because Excel would treat embedded commas as inadvertent list separators.
3. There was already an alphabetized list of employee names that was produced formulaically. I just needed to reference that named range.
4. Needed to handle the possibility that user might access dropdown after a selection had already been made. In such cases, it would be desirable to see the previously selected name as one of the choices.
5. Needed to handle the possibility that all names had been selected for a given date.

'This macro goes on code pane for worksheet Phone Time
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range, ActiveEmployeeNames As Range, AvailableNames As Range, LogDates As Range, LogNames As Range, targ As Range
Dim s As String
Dim v As Variant, vDate As Variant
Dim n As Long
Set LogDates = Range("C5")          'First cell in log with a date
Set LogNames = Range("D5")          'First cell in log with an employee name (should be on same rows as LogDates)
Set LogDates = Range(LogDates, Cells(Rows.Count, LogDates.Column).End(xlUp))
Set LogNames = LogNames.Resize(LogDates.Rows.Count, 1)
Set ActiveEmployeeNames = ThisWorkbook.Names("EmpActiveAlpha").RefersToRange
Set AvailableNames = ThisWorkbook.Names("EmpInActiveAlpha").RefersToRange   'List of available (not yet selected) employees for that date

Set targ = LogNames                 'Watch these cells for selections
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
    For Each cel In targ.Cells
        vDate = Intersect(cel.EntireRow, LogDates)
        If vDate <> "" And IsDate(vDate) Then
            With cel.Validation
                .Delete
                s = Available(cel.Offset(0, -1).Value, ActiveEmployeeNames, LogDates, LogNames, cel.Value)
                v = Split(s, "|")
                n = IIf(IsArray(v), UBound(v) + 1, 1)
                AvailableNames.ClearContents
                AvailableNames.Cells(1, 1).Resize(n, 1).Value = Application.Transpose(v)
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=EmpInActiveAlpha"
            End With
        End If
    Next
End If
End Sub

Open in new window

'This user-defined function goes on regular module sheet
Function Available(Date_ As Date, Active As Range, LogDates As Range, LogNames As Range, CurrentlySelectedEmp As String)
Dim emp As Variant
Dim col As Collection
Dim s As String
Dim i As Long, n As Long
For Each emp In Active.Value
    If (emp = CurrentlySelectedEmp) Or (Application.CountIfs(LogDates, Date_, LogNames, emp) = 0) Then
        s = s & "|" & emp
        n = n + 1
    End If
Next
If Len(s) > 0 Then
    Available = Mid(s, 2)
Else
    Available = "No employees available"
End If
End Function

Open in new window

Geekamo

ASKER

Hi Brad,

I was able to take some time out and go through the workbook - I did respond back via email asking you a couple questions. But overall, I don't think the changes will change your work all to much (but then again, I'm not entirely sure).

Let me know your thoughts!

byundt

With your revised workbook, I changed the code as shown below. Changes include:
1. Worksheet_SelectionChange: different columns for variables LogDates and LogNames
2. Worksheet_SelectionChange: different named range used for variable AvailableNames. This is so you could continue to use your formulas in named range EmpInActiveAlpha. I used named range SomeNewNamedRange instead, and put those values in column X adjacent to EmpInActiveAlpha.
3. Worksheet_SelectionChange: data validation dropdowns in column C now use named range SomeNewNamedRange instead of EmpInActiveAlpha
4. In user-defined function Available, I return a single space character if no employees are available for that date (instead of "No Employees available")

'This macro goes on code pane for worksheet Phone Time
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range, ActiveEmployeeNames As Range, AvailableNames As Range, LogDates As Range, LogNames As Range, targ As Range
Dim s As String
Dim v As Variant, vDate As Variant
Dim n As Long
Set LogDates = Range("B5")          'First cell in log with a date
Set LogNames = Range("C5")          'First cell in log with an employee name (should be on same rows as LogDates)
Set LogDates = Range(LogDates, Cells(Rows.Count, LogDates.Column).End(xlUp))
Set LogNames = LogNames.Resize(LogDates.Rows.Count, 1)
Set ActiveEmployeeNames = ThisWorkbook.Names("EmpActiveAlpha").RefersToRange
Set AvailableNames = ThisWorkbook.Names("SomeNewNamedRange").RefersToRange   'List of available (not yet selected) employees for that date

Set targ = LogNames                 'Watch these cells for selections
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
    For Each cel In targ.Cells
        vDate = Intersect(cel.EntireRow, LogDates)
        If vDate <> "" And IsDate(vDate) Then
            With cel.Validation
                .Delete
                s = Available(cel.Offset(0, -1).Value, ActiveEmployeeNames, LogDates, LogNames, cel.Value)
                v = Split(s, "|")
                n = IIf(IsArray(v), UBound(v) + 1, 1)
                AvailableNames.ClearContents
                AvailableNames.Cells(1, 1).Resize(n, 1).Value = Application.Transpose(v)
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=SomeNewNamedRange"    'Formula1 named range must match name used when defining AvailableNames
            End With
        End If
    Next
End If
End Sub

Open in new window

'This user-defined function goes on regular module sheet
Function Available(Date_ As Date, Active As Range, LogDates As Range, LogNames As Range, CurrentlySelectedEmp As String)
Dim emp As Variant
Dim col As Collection
Dim s As String
Dim i As Long, n As Long
For Each emp In Active.Value
    If (emp = CurrentlySelectedEmp) Or (Application.CountIfs(LogDates, Date_, LogNames, emp) = 0) Then
        s = s & "|" & emp
        n = n + 1
    End If
Next
If Len(s) > 0 Then
    Available = Mid(s, 2)
Else
    Available = " "     'Show a dropdown list that looks blank
End If
End Function

Open in new window

Geekamo

ASKER

Hi Brad,

I sent you an email.

Jason

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial