Validating dates against different date ranges based on user selection from a list

[KentishTim]

Hi all, first time here, complete amateur. I can do some VBA but I would like to do this by formula if possible.
My date formats below are yyyy-mm-dd

I am trying to do the following:
Sheet 1:
Column A: a user selects a customer from a drop down list of customers, so customer1, customer2...etc,. ,The first entry is in A1.
Column B: the user enters a date in column B for when an item was sent to the customer selected in A, so B1 is the date an item was sent.

Sheet 2:
I have 5 date ranges so
Column A Column B Column C
A1 Customer1 Start date 1 End date 1
Start date 2 End date 2
Start date 3 End date 3
Start date 4 End date 4
Start date 5 End date 5
Range of start dates is B1:B5 and range of end dates is C1:C5

A8: Customer2 Start date 1 End date 1
Start date 2 End date 2
Start date 3 End date 3
Start date 4 End date 4
Start date 5 End date 5
Range of start dates is B8:B12 and range of end dates is C8:C12

Each date range is 1 year from start date.
The start dates 1 through 5 are never likely to be the same for each customer as this relies on each of them signing and returning a document.
Equally there could be gaps or overlap between the ranges for a customer
e.g. Start date 1 = 2024-05-12 end date by default is 2025-05-12; there is then a delay before the customer signs their next paperwork.
Start date 2 = 2025-05-29 end date by default is 2026-05-29; this following year, they sign their next paperwork prior to the end date of that calendar year
Start date 3 = 2026-05-18 end date by default is 2027-05-18.

We can only send an item to a customer, within dates that are within their respective date ranges. I can do this when I only have one customer to consider, as there is only one date range.

How do i do this so that when customer1 is selected in Sheet 1A1 the date in Sheet1B1 is checked against the first set of date ranges and if customer 2 is selected in Sheet 1A1, the date in sheet1B1 is checked against the second set of date ranges; and so on.
I need to be able to this for more than 2 customers but it does not need to be hugely scalable. Upper limit is probably less than 10.

I can do the date validation checks when I only have one set of date ranges to consider using something like this:
=COUNTIFS($C$4:$C$8,"<"&Sheet2!C3,$D$4:$D$8,">"&Sheet2!C3)

but integrating that with the drop down selection and then the corresponding date in column B is the problem.

Thanks.

 

[p45cal]

If you copy down the customers in column A of Sheet2 (easy to do and I'll show you how if this suggestion is feasible) so that you have the customer repeating against all his dates, then you can can use one more pair of arguments in your countifs formula to check the customer name.
A bit of a guess:
=COUNTIFS($A$4:A$80, Sheet2!A3, $C$4:$C$80,"<"&Sheet2!C3,$D$4:$D$80,">"&Sheet2!C3)
although I'm a bit confused here between Sheet1 and Sheet2.

Best attach a workbook with the bare bones of what you're trying to do with some realistic data.
BTW, are your date ranges inclusive (do both start and end dates count as valid dates? If so pay special attention the > and < checking whether one or both might be >= and <=.

 

[KentishTim]

Hi @ P45Cal, thanks for coming back so quickly. The columns I had in my example collapsed when I submitted so it did not help.
Please see attached what I am trying to achieve. The PPT file shows this split over two worksheets as there is a good reason why I want it that way but the excel version shows it on one for convenience. If I can get it to work for one sheet I can figure out how to split it over 2.
The cell references in the excel sheet might make writing the answer easier as there are proper cells to refer to.

 

[p45cal]

I'm sorry, I can't make head nor tail of what you're wanting. Please attach workbook with somewhere an example of what you want.
What does this mean?:

The columns I had in my example collapsed when I submitted so it did not help.

I don't want a picture, I want an Excel workbok. I can't do a great deal with pictures.

 

[KentishTim]

Attached. Re the earlier comment, just ignore, I was explaining why my initial post was so gobbledegook.
See the PPT in the earlier post, it explains how I would like this to work.
If a user enters a date that an item is sent to a consignee (selected from the drop down), and that date is not in the one of the date ranges associated with that consignee, I would like the worksheet to flag that to the user.
Thanks again!

 

[p45cal]

I can only guess.

 

[KentishTim]

@p45cal, thanks that looks like it works. I'm going to be away for a few days so I won't have time to dive into it but I may come back to you mid next week for a bit of clarification on the methodology.
Thanks for your help, it really is greatly appreciated.