Trying to create a summary of data from another sheet

Hello All,
I have a main sheet, and I want another sheet to read it's data and when conditions are met, click a box. I had wanted it to read a specific date range, but that made it too specific to a year and would have to be changed the following year.
The formula below, won't work; ideally I would want a range of months like greater than and equal to April and less than and equal to May,
=IF(AND({Client Name (RH)}=[Client Name]@row, {Month}<= 5), 1, ""))
Also I was trying it another way and it is just missing it! Those damn parenthesis are the bane of my existence :)
IF(AND({#Rose Fert.} = "first", MATCH([Client Name]@row, {Client Name (RH)}), 1, ""))
Best Answer
-
Melissa, I had a pro session this morning, and you were right on. Here is what he came up with:
IFERROR(INDEX(COLLECT({RF#1}, {Client Name RH}, [Client Name]@row, {#Rose Fert.}, "First"), 1), 0)
Thank you again!
Answers
-
Hello @Craig Lemberger
Try this:
=IF(INDEX(COLLECT({Client Name (RH)}, {Month}, <= 5),1)=[Client Name]@row,1,"")
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
Thank you Melissa, I appreciate your effort. No I did not work. What I learned in the interim that one can't use Index if's going to be indexed multiple times; it needs to be unique, so i had to dispense with that function anyway.
-
Melissa, I had a pro session this morning, and you were right on. Here is what he came up with:
IFERROR(INDEX(COLLECT({RF#1}, {Client Name RH}, [Client Name]@row, {#Rose Fert.}, "First"), 1), 0)
Thank you again!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!