Auto populate date from helper sheet based on certain criteria

longlive289s

Hi All,

Having a heck of a time with a helper sheet and formula. If I type in a date, ex: 1/31/2023, I would like another column to populate a date that is before that 1/31/2022 date

The kicker is I need that date to be a specific day that I have listed on a helper sheet elsewhere (dates on the left "Board Meeting Dates")

I'm fighting with the vlookup/index/match functions to get smartsheet to auto populate "1/17/23" into that field since it is the closest prior date. Any help? :-/

Ryan Sides

Hi @longlive289s

If you want it to just highlight it for you:

Add a helper column on your sheet and name it "Same Dates". Make it a Checkbox column. In it, add this formula =if([Board Award Date]@row = [Notice to Proceed]@row, 1, 0)

Make that a column formula.

Then add a conditional format so that when Same Dates is checked, highlight the row in yellow.

But if you want it to grab the prior board date if the Motice to Proceed date is the Same as the Board date, then update your formula like this...

=IFERROR(IF(INDEX([Column5]:[Column5], MATCH([Column3]@row, [Column5]:[Column5], 0)) = [Column3]@row, INDEX([Column5]:[Column5], MATCH([Column3]@row - 1, [Column5]:[Column5], 1))), INDEX([Column5]:[Column5], MATCH([Column3]@row - 1, [Column5]:[Column5], 1)))

**Make sure to update this formula with your correct cross sheet reference names

Does that help?

Christian G.

This formula will give you the latest date before the notice to procedd date.

=MAX(COLLECT([Board Meeting date]:[Board meeting date],[Board Meeting date]:[Board meeting date], <=[Notice to Proceed]@row))

Ryan Sides

Hi @longlive289s

Did you create your cross sheet references?

Let's assume your cross-sheet reference is called {Board Meeting Date on Helper Sheet}.

Then your formula for your Board Award Date would look like this:

=INDEX({Board Meeting Date on Helper Sheet}, MATCH([Notice to Proceed]@row, {Board Meeting Date on Helper Sheet}, 1))

That will give you the closest matching date that's prior to your Notice to Proceed date.

Is that what you were going after?

Please let me know!

Ryan

longlive289s

Hi Ryan,

Thanks, that got it except I didn't expect one issue. How would I add an augmentation that doesn't allow board award date = notice to proceed date?

Christian G.

If you used my formula, remove the = .

=MAX(COLLECT([Board Meeting date]:[Board meeting date],[Board Meeting date]:[Board meeting date], <[Notice to Proceed]@row))

Ryan Sides

Hi @longlive289s

If you want it to just highlight it for you:

Add a helper column on your sheet and name it "Same Dates". Make it a Checkbox column. In it, add this formula =if([Board Award Date]@row = [Notice to Proceed]@row, 1, 0)

Make that a column formula.

Then add a conditional format so that when Same Dates is checked, highlight the row in yellow.

But if you want it to grab the prior board date if the Motice to Proceed date is the Same as the Board date, then update your formula like this...

=IFERROR(IF(INDEX([Column5]:[Column5], MATCH([Column3]@row, [Column5]:[Column5], 0)) = [Column3]@row, INDEX([Column5]:[Column5], MATCH([Column3]@row - 1, [Column5]:[Column5], 1))), INDEX([Column5]:[Column5], MATCH([Column3]@row - 1, [Column5]:[Column5], 1)))

**Make sure to update this formula with your correct cross sheet reference names

Does that help?

longlive289s

Thanks Ryan,

Had to mess with it a bit but yes this worked. Thank you so much!

Ryan Sides

@longlive289s You bet! I'm glad it helped out. - Ryan