Formula Request: Check if Dynamic Range falls Within Another Dynamic Range

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello!

Wondering if there's a way to setup what amounts to an error check for overlapping entries in my sheet. I have a large warehouse containing product from multiple clients, and a workbook for each client to account for how much space within the warehouse I've allotted to that particular client.

The screenshot below is my entry form for assigning space and consists of drop-down menus to select the Aisle and start/stop slot numbers I want to assign to the client. The first entry for example would be Aisle M1, slots 1-40. Some of my clients have 20 different locations, so I'd like a column that checks all previous entries (searches upwards) for any overlapping assignments. I can do a lot with formulas, but haven't one clue how to even approach this one. A basic boolean formula with true/false would be wonderful. Ultimately I'd like to take it a step further to actually list what locations are overlapping, but beggers can't be choosers.

As always, any help or direction would be greatly appreciated.

Thanks again!
Zach
 

Attachments

  • Untitled.png
    Untitled.png
    5.8 KB · Views: 2

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDE
1AccountAisleStartEndCheck
2abcM1140
3abcM2140FALSE
4abcM14150FALSE
5abcM14560TRUE
Master
Cell Formulas
RangeFormula
E3:E4E3=COUNTIFS(B$2:B2,B3,C$2:C2,"<="&C3,D$2:D2,">="&D3)>0
E5E5=COUNTIFS(B$2:B4,B5,C$2:C4,"<="&C5,D$2:D4,">="&C5)>0
 
Solution

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDE
1AccountAisleStartEndCheck
2abcM1140
3abcM2140FALSE
4abcM14150FALSE
5abcM14560TRUE
Master
Cell Formulas
RangeFormula
E3:E4E3=COUNTIFS(B$2:B2,B3,C$2:C2,"<="&C3,D$2:D2,">="&D3)>0
E5E5=COUNTIFS(B$2:B4,B5,C$2:C4,"<="&C5,D$2:D4,">="&C5)>0

Worked perfectly! Thank you so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,122
Messages
5,570,311
Members
412,318
Latest member
angoeyuan
Top