Cross-referencing sheets based on date and month of value

Hello,

I'm attempting to create a cross-sheet formula that inserts a date onto a target sheet depending on whether the month and date on the source sheet are between certain date ranges. More specifically, on my target sheet (let's call it sheet B), I have columns titled Spring Meeting, Summer Meeting, and Fall Meeting. For the spring column, I need it to reference a column titled Meeting Date on my source sheet (sheet A), then insert the date from that column onto sheet B but only if that date is between January 1 and May 15. I do not want it to factor the year into the equation. The same process would be used for the Summer Meeting and Fall meeting columns. A date should be added for the summer meeting column only if it falls between May 16 and August 19, and Fall should only be added to the Fall meeting column if the date on sheet A is between August 20 and December 31.

Any help is greatly appreciated!

Find more posts tagged with

Formulas

Accepted answers

Katey N.

This question has been resolved thanks to a ProDesk appointment.

With the use of a helper column I recently set up on the source sheet to identify the semester for the date ranges, instead of trying to go by the specific date ranges (e.g. 1/1-5/15), the formula the Smartsheet Pro suggested is

=MAX(COLLECT({Data Source Sheet Date Column}, {Data Source Sheet Name Column}, [Target Sheet Name Column]@row, {Data Source Sheet Semester Column}, "Semester Name"))

This allows me to have a "rolling" formula that doesn't account for year, it only pulls the most recent date from the desired semester.

All comments

Paul Newcome

Are you able to provide some screenshots with sample data manually entered for reference?

Katey N.

Hi Paul, here are a couple of screenshots. In the case below, if the name in the Student Name column of the Advisor Caseloads sheet matches the name in any of the four student name columns on the Advising Meeting Documentation sheet (not all four are shown in the screenshot), then the meeting date (4/27/23) on the Advising Meeting Documentation sheet should appear in the spring meeting column of the Advisor Caseloads sheet because the date falls between January 1 and May 15. If the date were between August 20 and December 31 then it would go to the Fall Meeting column, and if it were between May 16 and August 19 then it would go to the Summer Meeting column (not shown in the screenshot). Thanks in advance for your help!

Katey N.

This question has been resolved thanks to a ProDesk appointment.

=MAX(COLLECT({Data Source Sheet Date Column}, {Data Source Sheet Name Column}, [Target Sheet Name Column]@row, {Data Source Sheet Semester Column}, "Semester Name"))

This allows me to have a "rolling" formula that doesn't account for year, it only pulls the most recent date from the desired semester.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.