Solved

Need Formula Help, will involve lookups with conditions

Posted on 2015-01-31
20
80 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
ID: 40582757
is this only two names all the time? or the list can be many names?
0
 
LVL 18

Expert Comment

by:Simon
ID: 40582776
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
ID: 40583105
@ 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 81

Expert Comment

by:byundt
ID: 40583339
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
ID: 40590850
@ 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 81

Expert Comment

by:byundt
ID: 40591623
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
ID: 40591810
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 81

Expert Comment

by:byundt
ID: 40591884
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 81

Expert Comment

by:byundt
ID: 40592972
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
ID: 40593398
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:Geekamo
ID: 40593400
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 81

Expert Comment

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

Author Comment

by:Geekamo
ID: 40595200
Brad,

I sent you an email to your EE address.

Jason
0
 
LVL 81

Expert Comment

by:byundt
ID: 40595229
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
ID: 40595230
Ok, sending in just one moment.
0
 
LVL 81

Expert Comment

by:byundt
ID: 40604734
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
ID: 40613501
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 81

Expert Comment

by:byundt
ID: 40613640
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
ID: 40638939
Hi Brad,

I sent you an email.

Jason
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 40639211
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

21 Experts available now in Live!

Get 1:1 Help Now