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:
Examples of Invoices based on Data Set
Example #1
Example #2
Example #3
Example #4
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 Number | Customer Name | Date of Service | Month | Year | Service Description | Service Cost | Tips | Total Costs | Service Provider | Payment Method | Notes |
| 5 | Bobby | 2/2/2023 | February | 2023 | GENTLEMAN'S HAIRCUT | $19.00 | $19.00 | Hair Stylist - Savanah | VENMO | ||
| 1 | Adam | 2/2/2023 | February | 2023 | SHAMPOO & SET | $28.00 | $2.00 | $30.00 | Hair Stylist - Savanah | INVOICED | |
| 2 | Archer | 2/15/2023 | February | 2023 | NECK OR BANG TRIM | $10.00 | $10.00 | Hair Stylist - Savanah | INVOICED | Bang Only | |
| 4 | Helen | 2/15/2023 | February | 2023 | HAIR COLOR/SHAMPOO & SET | $75.00 | $75.00 | Hair Stylist - Savanah | ZELLE | ||
| 10 | Douglas | 2/12/2023 | February | 2023 | HAIR COLOR/SHAMPOO & SET | $75.00 | $75.00 | Hair Stylist - Savanah | VENMO | ||
| 8 | Burch | 2/12/2023 | February | 2023 | HAIR COLOR/SHAMPOO & SET | $75.00 | $75.00 | Hair Stylist - Savanah | LOCKBOX | ||
| 3 | Betty | 2/23/2023 | February | 2023 | SHAMPOO & SET | $28.00 | $12.00 | $40.00 | Hair Stylist - Savanah | INVOICED | |
| 9 | Corchran | 2/23/2023 | February | 2023 | 1/2 HAIR CUT | $14.00 | $14.00 | Hair Stylist - Savanah | VENMO | ||
| 5 | Bobby | 2/5/2023 | February | 2023 | MANICURE | $20.00 | $20.00 | Nail Technician - Faye | INVOICED | ||
| 1 | Adam | 2/28/2023 | February | 2023 | MANICURE | $20.00 | $20.00 | Nail Technician - Faye | INVOICED | ||
| 5 | Bobby | 3/2/2023 | March | 2023 | SHAMPOO & SET | $28.00 | $28.00 | Hair Stylist - Savanah | INVOICED | ||
| 1 | Adam | 3/2/2023 | March | 2023 | SHAMPOO & SET | $28.00 | $28.00 | Hair Stylist - Savanah | INVOICED |
Examples of Invoices based on Data Set
Example #1
| Invoice | 112 | ||||
| Name | Adam | ||||
| Customer Number | 1 | ||||
| Service Start Date | 2/1/2023 | ||||
| Service End Date | 2/28/2023 | ||||
| Invoice Date | 3/13/2023 | ||||
| Invoice To: | |||||
| Adam | |||||
| 123 Happy Lane | |||||
| Denver, CO 11122 | |||||
| 999-999-9999 | |||||
| Date of Service | Service Description | Service Costs | Tips | Total | Notes |
| 2/2/2023 | SHAMPOO & SET | $28.00 | $2.00 | $30.00 | |
| 2/28/2023 | MANICURE | $20.00 | $20.00 | ||
| Total Due | $48.00 | $2.00 | $50.00 |
Example #2
| Invoice | 113 | ||||
| Name | Archer | ||||
| Customer Number | 2 | ||||
| Service Start Date | 2/1/2023 | ||||
| Service End Date | 2/28/2023 | ||||
| Invoice Date | 3/13/2023 | ||||
| Invoice To: | |||||
| Archer | |||||
| 456 Joy Road | |||||
| Denver, CO 11122 | |||||
| 999-999-1010 | |||||
| Date of Service | Service Description | Service Costs | Tips | Total | Notes |
| 2/15/2023 | NECK OR BANG TRIM | $10.00 | $10.00 | Bang Only | |
| Total Due | $10.00 | $10.00 |
Example #3
| Invoice | 114 | ||||
| Name | Betty | ||||
| Customer Number | 3 | ||||
| Service Start Date | 2/1/2023 | ||||
| Service End Date | 2/28/2023 | ||||
| Invoice Date | 3/13/2023 | ||||
| Invoice To: | |||||
| Betty | |||||
| 789 Meadow View | |||||
| Denver, CO 11122 | |||||
| 999-999-1111 | |||||
| Date of Service | Service Description | Service Costs | Tips | Total | Notes |
| 2/23/2023 | NECK OR BANG TRIM | $28.00 | $12.00 | $40.00 | |
| Total Due | $28.00 | $12.00 | $40.00 |
Example #4
| Invoice | 115 | ||||
| Name | Bobby | ||||
| Customer Number | 5 | ||||
| Service Start Date | 2/1/2023 | ||||
| Service End Date | 2/28/2023 | ||||
| Invoice Date | 3/13/2023 | ||||
| Invoice To: | |||||
| Bobby | |||||
| 1011 Everest Blvd | |||||
| Denver, CO 11122 | |||||
| 999-999-1213 | |||||
| Date of Service | Service Description | Service Costs | Tips | Total | Notes |
| 2/5/2023 | MANICURE | $20.00 | $20.00 | ||
| Total Due | $20.00 | $20.00 |
I finally got it to work properly. I selected Felecia in C3 and got the message, "No data exists for Felecia for February 2023." Also, thanks for the instructions on how to enable events again without closing and re-opening Excel. That worked perfectly as well. I think I'm good to go now. I can't thank you enough!
