I need help with a formula
I am working on my {main sheet} and referencing a sheet named {tracking}
I am trying to return a value of a cell if the "dates" match and the "names" match in my {main sheet} and my {tracking sheet}, it should return a value from the {tracking sheet}.
Does anyone understand this?
Thank you,
D
Best Answer
-
Hi @Dakota
It looks like this is missing a range... I can't quite tell what your ranges are based on the name, but this would be the structure of an INDEX(COLLECT:
=INDEX(COLLECT({Value To Return}, {Column 1}, [Value 1]@row, {Column 2}, [Value 2]@row), 1)
So in your instance, we're either missing the column range that goes along with matching [Hidden Team:]@row, or we're missing the column to pull back information from.
=INDEX(COLLECT({Value To Return}, {Team Column}, [Hidden Team:]@row, {Date Column}, [Form Due Date:]@row), 1)
Does that help?
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hey Dakota, i do understand your problem, i believe i can help you, can you provide maybe a screen capture of both of your sheets?
Thanks
-
Top is {Main Sheet}
Bottom is {Tracking} You can see tracking sheet has #unparseable. Where the form due date is 02/04/2021 - I need the return value to = 10 - because on both sheets the team and the dates match.
-
Hi @Dakota
You'll want to use an INDEX(COLLECT formula to compare the different values and return a cell if all your values match.
Take a look at Paul's solution on this other post for an example: https://community.smartsheet.com/discussion/69141/can-you-use-2-match-criterias-in-an-index-match-formula
Let me know if you still need help!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks @Genevieve P. Iv been playing with that function plus others... I seem to always get back errors unperishable, incorrect functions, or incorrect arguments.
I must not be inputting this correctly....
=INDEX(COLLECT({Hours and Miles Form - Oil Change Sheet Range 3}, [Hidden Team:]@row, {Hours and Miles Form - Oil Change Sheet Range 5}, [Form Due Date:]@row), 1)
-
Hi @Dakota
It looks like this is missing a range... I can't quite tell what your ranges are based on the name, but this would be the structure of an INDEX(COLLECT:
=INDEX(COLLECT({Value To Return}, {Column 1}, [Value 1]@row, {Column 2}, [Value 2]@row), 1)
So in your instance, we're either missing the column range that goes along with matching [Hidden Team:]@row, or we're missing the column to pull back information from.
=INDEX(COLLECT({Value To Return}, {Team Column}, [Hidden Team:]@row, {Date Column}, [Form Due Date:]@row), 1)
Does that help?
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
=INDEX(COLLECT({Value To Return}, {Column 1}, [Value 1]@row, {Column 2}, [Value 2]@row), 1)
This I understand !!!
Thank you!!
-
Haha no problem at all! Glad I could help.
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.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!