Solved

# Need Formula Help, will involve lookups with conditions

Posted on 2015-01-31
75 Views
Hello Experts,

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.

EE-Example.xlsx
0
Question by:Geekamo

LVL 25

Expert Comment

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

LVL 18

Expert Comment

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

@ 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. :)

0

LVL 80

Expert Comment

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)
End With
End If
Next
End If
End Sub
``````
``````'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
``````
EE-ExampleQ28607759.xlsm
0

LVL 1

Author Comment

@ 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

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.

0

LVL 1

Author Comment

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

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

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.

0

LVL 1

Author Comment

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?

Thanks!
0

LVL 1

Author Comment

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

My email address is in my EE member profile.
0

LVL 1

Author Comment

Jason
0

LVL 80

Expert Comment

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

0

LVL 1

Author Comment

Ok, sending in just one moment.
0

LVL 80

Expert Comment

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)
End With
End If
Next
End If
End Sub
``````
``````'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
``````
0

LVL 1

Author Comment

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).

0

LVL 80

Expert Comment

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)
End With
End If
Next
End If
End Sub
``````

``````'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
``````
0

LVL 1

Author Comment

I sent you an email.

Jason
0

LVL 80

Accepted Solution

byundt earned 500 total points
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)
Me.Protect
End With
End If
Next
End If
End Sub
``````
0

## Featured Post

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.