Pulling a date if 2 cells match, and the date being pulled is within 7 days of a date column.

Looking for assistance on the formula below.
I'm pulling a date from a form sheet. The formula is working correctly up until the , AND function.
=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change She Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, [Truck #]@row, AND(ISDATE(@cell), @cell <= {2023 Hours and Miles Form - Oil Change She Range 5}, @cell >= [Tuesday Date]@row - 7)), 1)
Best Answer
-
@Dakota What column are you trying to reference with @cell? I think you're missing the range of that column prior to the AND statement. Also, what are you trying to do with the
@cell <= {2023 Hours and Miles Form - Oil Change She Range 5}
piece? I don't think that will work since it's comparing a value to a range.See below for the missing range (Range bold, criteria italic)
=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change She Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, [Truck #]@row, <<Missing Range>>, AND(ISDATE(@cell),
@cell <= {2023 Hours and Miles Form - Oil Change She Range 5}, @cell >= [Tuesday Date]@row - 7)), 1)
Answers
-
@Dakota What column are you trying to reference with @cell? I think you're missing the range of that column prior to the AND statement. Also, what are you trying to do with the
@cell <= {2023 Hours and Miles Form - Oil Change She Range 5}
piece? I don't think that will work since it's comparing a value to a range.See below for the missing range (Range bold, criteria italic)
=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change She Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, [Truck #]@row, <<Missing Range>>, AND(ISDATE(@cell),
@cell <= {2023 Hours and Miles Form - Oil Change She Range 5}, @cell >= [Tuesday Date]@row - 7)), 1) -
It works... Thank you Sharkasits! The missing range is the same range I am trying to pull. It works... I didn't know where the correct placement was for the range.
=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, {2023 Hours and Miles Form - Oil Change She Range 5}, AND(ISDATE(@cell), @cell >= [Tuesday Date]@row - 7)), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!