I am trying to return a value based on a couple of criteria. I'm using the index/collect method.
I have 2 sheets. Sheet 1 has a list of names. Sheet 2 has a list of locations, dates, and who is there across 6 columns. My goal is to have sheet 1 list where these people are currently located.
Sheet 1:
Sheet 2:
Formula for "Current Site" field on Sheet 1:
=index(collect({Sheet 2-Column1},{Sheet 2-Column30},"Yes",{Sheet 2-Tech 1-6},[Lead Tech]@row), 1)
Where Column 30 on Sheet 2 will say "Yes" when Today() falls within the date range. Thus if today was 6/29, Joe Smith would be at Location 2 with Phil Billson.
Best Answer
-
HI @snowmic1
You're correct, the COLLECT range won't be able to look into all 6 Tech columns in one range to search all of those cells for one name and then check your other columns - it would need each individual column to be referenced and checked separately.
What I would do in this instance is actually add a helper column to your main sheet with all the techs listed. If you add a Multi-Select column, you can use a JOIN formula to bring all the tech names into one cell as multiple, individual values. (You can then hide this column as it's not needed on that sheet).
=JOIN([Tech 1]@row:[Tech 6]@row, CHAR(10))
The CHAR(10) separates the names into multi-select values.
Then in your collect formula you can check to see if that one column, the multi-select column has the specific Lead Tech name (along with other names). This way you're still only referencing one column but you have all the values from that row to look at!
=INDEX(COLLECT({Sheet 2-Column1}, {Sheet 2-Column30},"Yes", {Sheet 2- Multi Select}, HAS(@cell, [Lead Tech]@row)), 1)
Let me know if this makes sense and will work for you.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
I can get it to work if I only reference one column when looking at [Lead Tech]@row. But then it's just coincidence of the name happens to be in that column and has a "Yes."
=index(collect({Sheet 2-Column1},{Sheet 2-Column30},"Yes",{Sheet 2-Tech 1},[Lead Tech]@row), 1)
This works but only coincidentally. It doesn't look at the whole row for the name. So it'll never tell me Joe Smith is at Location 2.
-
HI @snowmic1
You're correct, the COLLECT range won't be able to look into all 6 Tech columns in one range to search all of those cells for one name and then check your other columns - it would need each individual column to be referenced and checked separately.
What I would do in this instance is actually add a helper column to your main sheet with all the techs listed. If you add a Multi-Select column, you can use a JOIN formula to bring all the tech names into one cell as multiple, individual values. (You can then hide this column as it's not needed on that sheet).
=JOIN([Tech 1]@row:[Tech 6]@row, CHAR(10))
The CHAR(10) separates the names into multi-select values.
Then in your collect formula you can check to see if that one column, the multi-select column has the specific Lead Tech name (along with other names). This way you're still only referencing one column but you have all the values from that row to look at!
=INDEX(COLLECT({Sheet 2-Column1}, {Sheet 2-Column30},"Yes", {Sheet 2- Multi Select}, HAS(@cell, [Lead Tech]@row)), 1)
Let me know if this makes sense and will work for you.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!