help with an IF/AND that uses cross-sheet references.
Answers
-
Hello all, I'm looking for help with an IF/AND that uses cross-sheet references.
I have a MEMBER ROSTER main sheet like this:
And I have a lookup sheet GRANT YEARS like this:
I am trying to pull the correct "Grant Year" column from the GRANT YEARS sheet to the main sheet MEMBER ROSTER based on a value in the MEMBER ROSTER "Approved Date" column. If the MEMBER ROSTER "Approved Date" falls between the "Start Date" and "End Date" in GRANT YEARS, I want the matching "Grant Year" from GRANT YEARS pulled to the MEMBER ROSTER "Grant Year" column.
I have added these sheet references to MEMBER ROSTER:
I have this formula as a starting point in my MEMBER ROSTER "Grant Year" column --
=IF(AND([Approved Date]:[Approved Date] <= {GRANT YEARS | End Date}, [Approved Date]:[Approved Date] >= {GRANT YEARS | Start Date}), {GRANT YEARS | Grant Year},"unknown grant year")
-- which gives me #INVALID OPERATION. I think I need to be including some INDEX/MATCHes but I'm not sure where or how to use them here.
Any help would be appreciated.
-
Try this:
=MAX(COLLECT({Grant Year}, {Start Date}, @cell <= [Approved Date]@row))
-
This works - thank you! I hadn't considered using a combination of MAX and COLLECT.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!