Pulling Data from Column in Another Sheet
I am trying to pull data from a date column in another sheet - per location, month and year. I have tried the below formula with no success:
=IF({Center Name}, $[Center Name]@row, {Final Walk-Through Date}, MONTH(@cell) = $[Month #]@row, {Final Walk-Through Date}, YEAR(@cell) = $Year@row, COLLECT({Last Inspection Date}))
Any assistance is appreciated!
Answers
-
You are going to need an INDEX/COLLECT along the lines of...
=INDEX(COLLECT({Last Inspection Date}, {Center Name}, $[Center Name]@row, {Final Walk-Through Date}, MONTH(@cell) = $[Month #]@row, {Final Walk-Through Date}, YEAR(@cell) = $Year@row), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
The Screen shots are posted, I need the date "2024-01-08" from sheet one to appear where you see "unparseable" on sheet 2 next to "Edenton Ridge Apartments" if the row for "2024-01-08" contains "Edenton Ridge Apartments", Fire Drill Procedures" and "1st".
Thank you,
-
I agree that an INDEX(COLLECT is the way to go, here!
Try something with a structure like this:
=INDEX(COLLECT({Date Column to Return}, {Location Column}, [Quarterly Procedure Review Facilities]@row, {Procedure Reviewed Column}, "Fire Drill Procedure", {Shift Receiving Review Column}, HAS(@cell, "1st")), 1)
Keep in mind two things:
- If your column type that houses "2024-01-08" is a date type of column, the column you're typing this formula into should also be a date type of column or you'll get an error. You could add + "" to the end of the formula if you want to return the date as text instead.
- If you're using this for a list of different locations, across multiple columns, then I would suggest adding one "all locations" column in your source sheet where it simply returns whatever location is being listed in that row. You can hide it in your source sheet, but then reference it in this formula so your formula only needs to look in one column for any location.
Here's more information:
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!