Returning a date from another sheet using two criteria
I have two sheets, one being a Related sheet that has three columns:
Number, Label, Date
Then there is a Main Sheet that also has
Number, Label, Date (plus other columns)
This is simulating a One-to-Many relationship as the Related Sheet can have multiple entries for each Number which I want to collect into multiple columns on one row into the main sheet dependant on what the Label is in the Related Sheet.
I want to search for the matching number and label values and return the date from the Related sheet
Is there a way to match multiple columns and return a value (Date) other than using
=JOIN(COLLECT({Related Sheet Date}, {Related Sheet Num}, [Main Sheet Number]@row, {Related Sheet Label}, "Label 1"))
which returns the Date as a string and then I have to have a string column to return that into and then use a formula to convert the string to a date, such as the formula:
=IF(NOT(ISBLANK(DateStr@row)), DATE(VALUE("20" + RIGHT(DateStr@row, 2)), VALUE(MID(DateStr@row, 4, 2)), VALUE(LEFT(BDStr@row, 2))))
Doing this sometime causes errors temporarily (until a reload and save) in the Main sheet due to the Australian date format being DD/MM/YYYY and there being instances where the date is returned in the US format MM/DD/YYYY which is invalid for DD greater than 12. Might have something to do with hosting on US servers.
Not to mention it is a lot of unnecessary calculating and extra columns which is cumbersome.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Best Answer
-
Hi @Frank Falco
What about using an INDEX(COLLECT instead of JOIN? JOIN will turn the Date into string and will add together multiple dates. INDEX will find one value and can be returned into a Date type of column... although if there's more than one match it will only find the first one.
The structure is almost exactly the same:
=INDEX(COLLECT({Related Sheet Date}, {Related Sheet Num}, [Main Sheet Number]@row, {Related Sheet Label}, "Label 1"), 1)
It just needs the 1 at the end to indicate it should pull the first match back. Would this work for you?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Frank Falco
What about using an INDEX(COLLECT instead of JOIN? JOIN will turn the Date into string and will add together multiple dates. INDEX will find one value and can be returned into a Date type of column... although if there's more than one match it will only find the first one.
The structure is almost exactly the same:
=INDEX(COLLECT({Related Sheet Date}, {Related Sheet Num}, [Main Sheet Number]@row, {Related Sheet Label}, "Label 1"), 1)
It just needs the 1 at the end to indicate it should pull the first match back. Would this work for you?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you @Genevieve P. that worked nicely.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
Wonderful! Thanks for letting me know 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I found this thread and have a related issue that I cannot figure out. I have a formula very similar to what you proposed, but it is returning an "INVALID VALUE". Please help me figure out what I am doing wrong.
=INDEX(COLLECT({Project Timeline Range 1}, {Project Timeline Range 2}, [Project Info]@row, {Project Timeline Range 3}, [Project Name]@row), 1)
I am trying to pull the date from the Project Timeline sheet which corresponds to both the task of Develop Submittal Drawings, and which has the matching Project Name. My Range which I am trying to pull from is the Start Date column, which is pulling from the Start column via =Start@row, because I read that you cannot lookup information in a column which is used in dependencies.
-
Good news: you can actually reference a Date column in another sheet in a formula, even if that column is used with dependencies. I believe the limitation you're describing is being able to put a formula in that column (versus referencing/looking at it from a separate formula).
Can you double check that all of your columns are Date type of columns? (The one the formula is placed in, as well as what it's looking at).
The next thing to check is to ensure there's a match with your criteria. I see your project name is set to "Test Test" - is that the correct project name?
You can ensure there's a match by using a COUNTIFS formula with your same criteria:
=COUNTIFS({Project Timeline Range 2}, [Project Info]@row, {Project Timeline Range 3}, [Project Name]@row))
If you get 0, then your INDEX(COLLECT formula would error as there's no match. You can wrap an IFERROR function around it to return that text instead of an error:
=IFERROR(INDEX(COLLECT({Project Timeline Range 1}, {Project Timeline Range 2}, [Project Info]@row, {Project Timeline Range 3}, [Project Name]@row), 1), "No Match")
Let me know if this helped!
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
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!