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!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

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)

brent.kendall

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,

Genevieve P.

Hi @brent.kendall

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:

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.