How to pull the next due date from a list of up coming dates from a different sheet

JBerm

I am fairly new to Smartsheet and still getting a feel for the landscape. I have two sheets, one is my main sheet where I want the dates to be pulled into, the second sheet is where I have my upcoming due dates. Essentially what I am trying to do is add a formula on my main sheet in the "Next Action Item" Column, where it will look at the current year "Year of Project" and pull the in the next due date from the list of dates in the second sheet when.

The current formula I am trying to use in the "Next Action Item Date" column starts with first identifying the "Year of Project" column, and then thinking a MIN(COLLECT would be the next logical formula, but running into issues:

=IF([Year of Project]@row = "1st year", MIN(COLLECT({Example}@row:{Example}@row, {Example}@row:{Example}@row, @cell > TODAY()))

The goal is when 1st year is selected it will look at sheet 2 and pull from the 1st year dates the next upcoming date when it is within 5 days. When 2nd year is selected it will look at the column of sates for that specific year.

The formula is referencing sheet 2

Sheet 1 (Main)

Sheet 2 (Data sheet)

Kelly Moore

Hey John

It looks like I forgot to close the MIN function. Try adding a closing parenthesis before the 2nd year IF statement. You'll have to remove a parenthesis from the very end as well

Kelly Moore

Hey @JBerm

You're on the right track with the Min/Collect. We need to make some adjustments to the syntax of your formula. To make sure we're referring to the same data ranges, I've changed the names of the ranges to be explicit. I'm also going to assume that your data sheet has some sort of status column that indicates that the action is incomplete or complete- however you have that labelled and will assume you only want dates that refer to incomplete actions. If this isn't true, you can eliminate those terms from the formula below.

=IF([Year of Project]@row = "1st year", MIN(COLLECT({data sheet Key Dates (AC)}, {data sheet Key Dates (AC)}, ISDATE(@cell), {data sheet Key Dates (AC)}, @cell > TODAY(), {data sheet Key Dates (AC)},<=TODAY(5), {data sheet status column}, "Incomplete")), IF([Year of Project]@row = "2nd year", MIN(COLLECT({data sheet Key Dates (AC2)}, {data sheet Key Dates (AC2)}, ISDATE(@cell), {data sheet Key Dates (AC2)}, @cell > TODAY(), {data sheet Key Dates (AC2)},<=TODAY(5), {data sheet status column}, "Incomplete"))))

Does this get you close to what you need? We can tweak it.

Kelly

JBerm

Kelly,

Thank you for responding. So the formula worked, I just made some minor adjustments and removed the incomplete/complete columns I am tracking the status in another cell based on the date. The formula looks like this.

=IF([Year of Project]@row = "1st year", MIN(COLLECT({Key Dates (AC)}, {Key Dates (AC)}, ISDATE(@cell), {Key Dates (AC)}, @cell >= TODAY(), {Key Dates (AC)}, <=TODAY(5)), IF([Year of Project]@row = "2nd year", MIN(COLLECT({Key Dates (AC2)}, {Key Dates (AC2)}, ISDATE(@cell), {Key Dates (AC2)}, @cell > TODAY())))))

Below I will show the working functionality.

(First two images are before the new formula was implemented)

The formula was implemented in the below images:

However the issue with this formula

=IF([Year of Project]@row = "1st year", MIN(COLLECT({Key Dates (AC)}, {Key Dates (AC)}, ISDATE(@cell), {Key Dates (AC)}, @cell >= TODAY(), {Key Dates (AC)}, <=TODAY(5)), IF([Year of Project]@row = "2nd year", MIN(COLLECT({Key Dates (AC2)}, {Key Dates (AC2)}, ISDATE(@cell), {Key Dates (AC2)}, @cell > TODAY())))))

Is when I select "2nd year" from the "Year of Project" Column as depicted below

IT doesn't appear to pull the dates from the "Key Dates (AC2)" column from the metric sheet, it just populates with nothing. I however pulled the second part of the formula and used it as a stand alone in another row to ensure it works and the syntax is right and it worked fine. Is there something missing here when nesting the two IF statements together and why is not populating the dates for the "Key Dates (AC2)" when "2nd year" is selected.

Thank you in advance,

John

Kelly Moore

Hey John

It looks like I forgot to close the MIN function. Try adding a closing parenthesis before the 2nd year IF statement. You'll have to remove a parenthesis from the very end as well

JBerm

Thank you very much! That was it.