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: 8

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
Solution
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!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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