Solved

Need Formula Help, will involve lookups with conditions

Posted on 2015-01-31
20
75 Views
Last Modified: 2016-02-13
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
0
Comment
Question by:Geekamo
20 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
is this only two names all the time? or the list can be many names?
0
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
@ 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
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
@ 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!
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
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?
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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.
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
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!
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:Geekamo
Comment Utility
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!
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
My email address is in my EE member profile.
0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
Brad,

I sent you an email to your EE address.

Jason
0
 
LVL 80

Expert Comment

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

Brad
0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
Ok, sending in just one moment.
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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

0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
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!
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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

0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
Hi Brad,

I sent you an email.

Jason
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
Jason,
In response to your email, I modified the Worksheet_SelectionChange macro to handle protected worksheets:
'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)
                If s = "" Then
                    ReDim v(0)
                    v(0) = " "
                    n = 1
                Else
                    v = Split(s, "|")
                    n = IIf(IsArray(v), UBound(v) + 1, 1)
                End If
                Me.Unprotect
                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
                Me.Protect
            End With
        End If
    Next
End If
End Sub

Open in new window

 Brad
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now