Pull Date from Reference sheet by using collect option
Hi, I'm trying to pull date from another sheet (Sheet2) to Sheet1 using Collect option but the value is showing error as #DATE EXPECTED. I'm not sure where i'm missing the value.
Sheet1:
ERROR = #DATE EXPECTED
FORMULA: =COLLECT({Date Range}, {Name Range}, [Trainee Name]@row, {Training Name Range}, "Training1")
Sheet2:
Can i get some help here please.
Best Answer
-
Try: =JOIN(COLLECT({Date Range}, {Name Range}, [Trainee Name]@row, {Training Name Range}, "Training1"))
COLLECT needs to be used with another function. Use JOIN to bring in text.
Your formula will only work if there's 1 date found. If it returns 2 dates you'll get an error.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Try: =JOIN(COLLECT({Date Range}, {Name Range}, [Trainee Name]@row, {Training Name Range}, "Training1"))
COLLECT needs to be used with another function. Use JOIN to bring in text.
Your formula will only work if there's 1 date found. If it returns 2 dates you'll get an error.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Perfect, its working. Thanks so much for the quick help. Much appreciated🙏
-
Excellent. Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 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!