Extract Rows of Data Using VLOOKUP With Multiple Criteria and Import to an Invoice for Each Customer

S

scoobydoo

New Member
Joined
Jan 6, 2010
Messages
25
Office Version
  1. 2007
Platform
  1. Windows
Hello, I'm seeking assistance in trying extract rows of data using vlookup with multiple criteria for a particular date range and import information into an invoice for each customers. I have a list of all salon customers and services rendered starting 2/1/2023 which I will update each month. I'm trying to create an invoice for each customers based on looking up Customer Name, Date Range for each month, i.e. 2/1/2023 thru 2/28/2023, and Payment Method = INVOICED. I would like to extract the Date of Service, Service Description, Service Costs, Tips and add Total. Please see below an example of the data set as well as the invoice.

I've worked out the part where I pull the customer's information into the invoice. However, I'm having difficulties figuring out how to get the list of services provided for a particular customer by date during the month.

Any assistance someone can provide will be greatly appreciated.

Data Set Example:

Customer NumberCustomer NameDate of ServiceMonthYearService DescriptionService CostTipsTotal CostsService ProviderPayment MethodNotes
5Bobby2/2/2023February2023GENTLEMAN'S HAIRCUT$19.00$19.00Hair Stylist - SavanahVENMO
1Adam2/2/2023February2023SHAMPOO & SET$28.00$2.00$30.00Hair Stylist - SavanahINVOICED
2Archer2/15/2023February2023NECK OR BANG TRIM$10.00$10.00Hair Stylist - SavanahINVOICEDBang Only
4Helen2/15/2023February2023HAIR COLOR/SHAMPOO & SET$75.00$75.00Hair Stylist - SavanahZELLE
10Douglas2/12/2023February2023HAIR COLOR/SHAMPOO & SET$75.00$75.00Hair Stylist - SavanahVENMO
8Burch2/12/2023February2023HAIR COLOR/SHAMPOO & SET$75.00$75.00Hair Stylist - SavanahLOCKBOX
3Betty2/23/2023February2023SHAMPOO & SET$28.00$12.00$40.00Hair Stylist - SavanahINVOICED
9Corchran2/23/2023February20231/2 HAIR CUT$14.00$14.00Hair Stylist - SavanahVENMO
5Bobby2/5/2023February2023MANICURE$20.00$20.00Nail Technician - FayeINVOICED
1Adam2/28/2023February2023MANICURE$20.00$20.00Nail Technician - FayeINVOICED
5Bobby3/2/2023March2023SHAMPOO & SET$28.00$28.00Hair Stylist - SavanahINVOICED
1Adam3/2/2023March2023SHAMPOO & SET$28.00$28.00Hair Stylist - SavanahINVOICED

Examples of Invoices based on Data Set

Example #1
Invoice112
NameAdam
Customer Number1
Service Start Date2/1/2023
Service End Date2/28/2023
Invoice Date3/13/2023
Invoice To:
Adam
123 Happy Lane
Denver, CO 11122
999-999-9999
Date of ServiceService DescriptionService CostsTipsTotalNotes
2/2/2023SHAMPOO & SET$28.00$2.00$30.00
2/28/2023MANICURE$20.00$20.00
Total Due$48.00$2.00$50.00


Example #2
Invoice113
NameArcher
Customer Number2
Service Start Date2/1/2023
Service End Date2/28/2023
Invoice Date3/13/2023
Invoice To:
Archer
456 Joy Road
Denver, CO 11122
999-999-1010
Date of ServiceService DescriptionService CostsTipsTotalNotes
2/15/2023NECK OR BANG TRIM$10.00$10.00Bang Only
Total Due$10.00$10.00


Example #3
Invoice114
NameBetty
Customer Number3
Service Start Date2/1/2023
Service End Date2/28/2023
Invoice Date3/13/2023
Invoice To:
Betty
789 Meadow View
Denver, CO 11122
999-999-1111
Date of ServiceService DescriptionService CostsTipsTotalNotes
2/23/2023NECK OR BANG TRIM$28.00$12.00$40.00
Total Due$28.00$12.00$40.00


Example #4
Invoice115
NameBobby
Customer Number5
Service Start Date2/1/2023
Service End Date2/28/2023
Invoice Date3/13/2023
Invoice To:
Bobby
1011 Everest Blvd
Denver, CO 11122
999-999-1213
Date of ServiceService DescriptionService CostsTipsTotalNotes
2/5/2023MANICURE$20.00$20.00
Total Due$20.00$20.00
 
Sort by date Sort by votes
mumps said:
You are very welcome. :)
Click to expand...
Hello Mumps! Everything was working well in the sample file and real data set initially. I selected a name that was not in the sample data file, which is when the problems began. I am now getting the following error Run-time error '91': Object variable or With block variable not set. Please see attached file. It prompts me to enter the month and year. It populated the Customer Number, Service Start Date, Service End Date, and Invoice Date. However, it did not populate the Invoice To: Section nor the Invoice Details Section (Date, Service Type, Service Cost, Tips, Total and Notes). If you could, please let me know how to fix this error. Thanks!
 

Attachments

  • VBA Error Salon Invoice.png
    VBA Error Salon Invoice.png
    29.4 KB · Views: 5
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
scoobydoo said:
I selected a name that was not in the sample data file
Click to expand...
Are you saying that you selected a name in C3 of the Real Invoice Template sheet and the selected name was not in the "Customer Contact Information" sheet? Please clarify in detail.
 
Upvote 0
mumps said:
Are you saying that you selected a name in C3 of the Real Invoice Template sheet and the selected name was not in the "Customer Contact Information" sheet? Please clarify in detail.
Click to expand...
I selected a name in C3 of the Real Invoice Template that was not in the Data Set (Service Log) sheet. There are other customers on my roster that do not receive services every month. I hope I've explained it better. Please let me know if you have additional questions. Thanks for your help!
 
Upvote 0
The drop down list in C3 is created based on all the values in column A of the "Data Set (Service Log)" sheet. This means that any name you select in C3 will always be found in the Data Set (Service Log) sheet. How are you creating the drop down list in C3? If it not based on all the values in column A of the "Data Set (Service Log)" sheet, please upload a revised copy of the actual file you are using.
 
Upvote 0
mumps said:
The drop down list in C3 is created based on all the values in column A of the "Data Set (Service Log)" sheet. This means that any name you select in C3 will always be found in the Data Set (Service Log) sheet. How are you creating the drop down list in C3? If it not based on all the values in column A of the "Data Set (Service Log)" sheet, please upload a revised copy of the actual file you are using.
Click to expand...
The drop down list in the C3 in the real data set is created from a directory of all customers worksheet. I can try and change the data source of my drop down list in C3 to be Column A of the "Data Set (Service Log)" sheet. I will also create a sample of how I currently have it set up.
 
Upvote 0
scoobydoo said:
The drop down list in the C3 in the real data set is created from a directory of all customers worksheet. I can try and change the data source of my drop down list in C3 to be Column A of the "Data Set (Service Log)" sheet. I will also create a sample of how I currently have it set up.
Click to expand...
Hello Mumps,

I just uploaded a revised sample file, "scoobydoo mumps_Scoobydoo Revised" which is exactly how my file should work. Here's the link: scoobydoo mumps_Scoobydoo Revised.xls

I added a Customer Directory worksheet, which is sorted in alphabetical order to make it easier to select customers from the drop down list in C3 on the Real Invoice Template worksheet. I didn't use the Customer Contact Information worksheet, due to this worksheet only will contain customers who have a responsible party making payments after being invoiced. Also, I didn't use the Data Set (Service Log) worksheet to create the list for C3 in the the Real Invoice Template worksheet because the names are not listed in alphabetical order.

This is the link to my Dropbox Salon Files Help Folder: Salon Files Help

Thanks, again!
 
Upvote 0
Thanks for the file. What do you want to do if the name selected is not found in the "Customer Contact Information" sheet?
 
Upvote 0
mumps said:
Thanks for the file. What do you want to do if the name selected is not found in the "Customer Contact Information" sheet
Click to expand...
If
mumps said:
Thanks for the file. What do you want to do if the name selected is not found in the "Customer Contact Information" sheet?
Click to expand...
If the name selected is not found in the "Customer Contact Information" sheet, we can say "No Responsible Party Info Found". Please let me know if that is possible. This will let me know that we need to enter/update "Customer Contact Information" sheet for that customer, or double check the Payment Method to ensure Payment Method = "INVOICED". Thanks!
 
Upvote 0
Try:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "C3" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim srcWS As Worksheet, CCI As Worksheet, mon As String, yr As String
    Dim fVisRow As Long, lRow As Long, lRow2 As Long, monNum As Long, rName As Range
    Set srcWS = Sheets("Data Set (Service Log)")
    Set CCI = Sheets("Customer Contact Information")
    Set rName = CCI.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not rName Is Nothing Then
        lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        mon = InputBox("Enter the month name.")
        If mon = "" Then Exit Sub
        If WorksheetFunction.CountIf(srcWS.Range("D:D"), mon) = 0 Then
            MsgBox ("Invalid month.  Please try again.")
            Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
        yr = InputBox("Enter the year.")
        If yr = "" Then Exit Sub
        If WorksheetFunction.CountIf(srcWS.Range("E:E"), yr) = 0 Then
            MsgBox ("Invalid year.  Please try again.")
            Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
        monNum = Month(DateValue("01 " & mon & " " & yr))
        With srcWS.Range("A1").CurrentRegion
            .AutoFilter 2, Target
            .AutoFilter 4, mon
            .AutoFilter 5, yr
            If srcWS.[subtotal(103,A:A)] - 1 = 0 Then
                MsgBox ("No data exists for " & Target & " for " & mon & " " & yr & ".")
                Target.ClearContents
                .AutoFilter
                Application.EnableEvents = True
                Exit Sub
            End If
        End With
        ActiveSheet.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = Target & " " & mon & "-" & yr
        fVisRow = srcWS.Rows("2:" & lRow).SpecialCells(xlCellTypeVisible).Row
        With ActiveSheet
            .Range("C4") = srcWS.Range("A" & fVisRow)
            .Range("C5") = DateSerial(yr, monNum, 1)
            .Range("C6") = Application.WorksheetFunction.EoMonth(srcWS.Range("C" & fVisRow), 0)
            .Range("C5:C6").NumberFormat = "mm-dd-yyyy"
            .Range("C7") = Date
            .Range("B11") = rName.Offset(, 2)
            .Range("C13") = rName.Offset(, 8)
            .Range("B12").Resize(3).Value = WorksheetFunction.Transpose(Array(rName.Offset(, 3), rName.Offset(, 5), rName.Offset(, 8)))
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("C:C").SpecialCells(xlCellTypeVisible)).Copy .Range("B20")
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("F:I").SpecialCells(xlCellTypeVisible)).Copy .Range("D20")
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("L:L").SpecialCells(xlCellTypeVisible)).Copy .Range("H20")
            .Range("G40").Formula = "=sum(G20:G36)"
            .Range("G41").Formula = "=sum(F20:F36)"
            .Range("G42").Formula = "=sum(G40:G41)"
            .Columns.AutoFit
        End With
        srcWS.Range("A1").AutoFilter
    Else
        MsgBox ("No Responsible Party Info Found for " & Target & ".")
    End If
    Target.ClearContents
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
Try:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "C3" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim srcWS As Worksheet, CCI As Worksheet, mon As String, yr As String
    Dim fVisRow As Long, lRow As Long, lRow2 As Long, monNum As Long, rName As Range
    Set srcWS = Sheets("Data Set (Service Log)")
    Set CCI = Sheets("Customer Contact Information")
    Set rName = CCI.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not rName Is Nothing Then
        lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        mon = InputBox("Enter the month name.")
        If mon = "" Then Exit Sub
        If WorksheetFunction.CountIf(srcWS.Range("D:D"), mon) = 0 Then
            MsgBox ("Invalid month.  Please try again.")
            Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
        yr = InputBox("Enter the year.")
        If yr = "" Then Exit Sub
        If WorksheetFunction.CountIf(srcWS.Range("E:E"), yr) = 0 Then
            MsgBox ("Invalid year.  Please try again.")
            Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
        monNum = Month(DateValue("01 " & mon & " " & yr))
        With srcWS.Range("A1").CurrentRegion
            .AutoFilter 2, Target
            .AutoFilter 4, mon
            .AutoFilter 5, yr
            If srcWS.[subtotal(103,A:A)] - 1 = 0 Then
                MsgBox ("No data exists for " & Target & " for " & mon & " " & yr & ".")
                Target.ClearContents
                .AutoFilter
                Application.EnableEvents = True
                Exit Sub
            End If
        End With
        ActiveSheet.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = Target & " " & mon & "-" & yr
        fVisRow = srcWS.Rows("2:" & lRow).SpecialCells(xlCellTypeVisible).Row
        With ActiveSheet
            .Range("C4") = srcWS.Range("A" & fVisRow)
            .Range("C5") = DateSerial(yr, monNum, 1)
            .Range("C6") = Application.WorksheetFunction.EoMonth(srcWS.Range("C" & fVisRow), 0)
            .Range("C5:C6").NumberFormat = "mm-dd-yyyy"
            .Range("C7") = Date
            .Range("B11") = rName.Offset(, 2)
            .Range("C13") = rName.Offset(, 8)
            .Range("B12").Resize(3).Value = WorksheetFunction.Transpose(Array(rName.Offset(, 3), rName.Offset(, 5), rName.Offset(, 8)))
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("C:C").SpecialCells(xlCellTypeVisible)).Copy .Range("B20")
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("F:I").SpecialCells(xlCellTypeVisible)).Copy .Range("D20")
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("L:L").SpecialCells(xlCellTypeVisible)).Copy .Range("H20")
            .Range("G40").Formula = "=sum(G20:G36)"
            .Range("G41").Formula = "=sum(F20:F36)"
            .Range("G42").Formula = "=sum(G40:G41)"
            .Columns.AutoFit
        End With
        srcWS.Range("A1").AutoFilter
    Else
        MsgBox ("No Responsible Party Info Found for " & Target & ".")
    End If
    Target.ClearContents
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Click to expand...
Thanks, Mumps! The code worked fine up until the point where I select "Sharon" in C3 to test the "No Responsible Party Info Found". I entered data for Sharon on the Data Set (Service Log) worksheet for February 14, 2023 and VENMO as the Payment Method. I did not enter data for Sharon on the Customer Contact Information worksheet.

I got the following error: Run-time error '91' Object variable or With block variable not set.

I really appreciate the time you've dedicated to helping me with this project. If you have time to look into this, it will be greatly appreciated. If not, I will try and make sure to verify all customers requesting "INVOICED" Payment Method have Customer Contact Information.

Thanks a million!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
934
paulkc
P
K
  • Question Question
populate amounts for column debit,credit for each customer from file to another
Replies
0
Views
349
KalilMe
K
S
  • Question Question
Need help creating a multiple tab invoice from a raw data file
Replies
1
Views
344
sreed39
S
K
  • Solved
extraction invoices detail for TOTAL row for each sheet individual
Replies
7
Views
374
RudRud
R
K
  • Question Question
compare data across sheets for 6000 rows for each sheet individual based on sheet name
Replies
0
Views
293
KalilMe
K

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top