Dual parameter formula to capture last entered date versus last date of column?

Adam Kinney

Currently have a sheet that allows an end user to utilize a drop down and date column to quickly input their activity related to a row. Once entered, a workflow copies the date and activity to a separate sheet with dates placed into individual columns. Using a MAX/COLLECT reference formula I am able to retrieve the last date of the reference column so that as the activity type and date changes it populates across columns with conditional formatting for a visual reference.

My problem is that if the last activity date is saved as shown above as [Last Activity]8="11/11/20" with [Last Activity Type]8 = "Initial Presentation" and I need to change the date to an earlier date of 11/8/20, the date in the [Initial Presentation] column will keep returning the latest date due to the MAX formula.

How can I adjust the formula to select the most recent date changed in the reference column versus the latest?

Current formula is:

=MAX(COLLECT({ROLL UP TEST Range 1}, {ROLL UP TEST Range 2}, [Account No.]@row, {COPY COPY ROLL UP TEST Range 1}, KAE@row))

@Genevieve P any ideas?

Adam Kinney

Thinking I need to add in a dual parameter that includes the [Modified] column with a reference of =MAX([Modified]:[Modified]=TODAY()) this way it reads the MAX date in the column that was modified on the same day?

SoS | Dan Palenchar

I was going to suggest using Modified as the reference field in your Collect function.

=MAX(COLLECT(Modified:Modified, ..., ...))

I'm not completely sure without seeing everything working together, the part where you have =TODAY() might not be necessary and perhaps will omit relevant data.

Adam Kinney

@Dan Palenchar returned an error.

Genevieve P.

Hi @Adam Kinney

I'm not sure I quite understand correctly what's going on... it looks like you're referencing the Modified column within this current sheet, but the Modified column indicates which row is modified recently, without pulling a specific cell. This means that it wouldn't be able to return a specific date from its own row into that "Last Activity Date" column, since the whole row would be modified recently. Is that what you're looking to do?

Would you be able to describe exactly what you want seen in that "Last Activity Date" column, preferably with a screen capture of the other sheet, if possible (but please block out any sensitive data!).

Cheers,

Genevieve

Adam Kinney

@Genevieve P What I am wanting the formula to do is to pull the most recently updated date as the [Last Activity Date] column is updated or changed. Currently it works well if the date never needs to be adjusted to a date prior to what was retrieved by the current formula.

The bottom row example above shows what is happening when a [Last Activity Date] is changed to a date prior to the last entry on the end users sheet. Current formula in the [Intitial Presentation] column is:

=MAX(COLLECT({ROLL UP TEST Range 1}, {ROLL UP TEST Range 2}, [Account No.]@row, {COPY COPY ROLL UP TEST Range 1}, KAE@row))

It is referencing this sheet that is updated via a workflow to add new rows every time there is a new row added or changed on the end users sheet :

The column formula for the referenced sheet [Initial Presentation] column is (same for other columns):

=IF(AND([Last Activity Type]@row = "Survey", ISDATE([Last Activity Date]@row)), [Last Activity Date]@row, 0

Meaning if my end user enters a date of 11/12/2020 with an activity type of Initial Presentation, that date is pushed to the reference sheet as a new row. Works great if you never have to change a date back to a prior date than what was previously entered, hence my issue. Need it to pull the most recent date entered on the end user sheet so that if they make a mistake they can go back and change the date and it reflects properly on their sheet in the formula based 'static' columns (columns with conditional formatting in first image). Make sense?

Genevieve P.

@Adam Kinney

OK! I think I understand... so you need to find the most recent row with all that criteria, and then pull the date from that column. You're right, adding in a Modified or Created System column to the second sheet pictured above could work!

You'll need to add in the new range, in my example below it's {Created Date}

Then the criteria of this column is the MAX date, but only based on certain criteria. So essentially we would re-peat and embed a second MAX(COLLECT within your MAX(COLLECT.

Try this:

=MAX(COLLECT({ROLL UP TEST Range 1}, {ROLL UP TEST Range 2}, [Account No.]@row, {COPY COPY ROLL UP TEST Range 1}, KAE@row, {Created Date}, MAX(COLLECT({Created Date}, {ROLL UP TEST Range 2}, [Account No.]@row, {COPY COPY ROLL UP TEST Range 1}, KAE@row))))

Does that make sense? Can you test it and see if it works for you?

Cheers!

Genevieve

Adam Kinney

@Genevieve P I may be screwing this up. Is the {Created Date} referencing the roll up sheet that new rows are pushed to when the end user changes dates or supposed to reference the end users sheet?

=MAX(COLLECT({COPY COPY ROLL UP TEST Range 1}, {COPY COPY ROLL UP TEST Range 2}, [Account No.]@row, {COPY COPYROLL UP TEST Range 1}, KAE@row, {COPY COPY ROLL UP TEST Range 3}, MAX(COLLECT({COPY COPY ROLL UP TEST Range 3}, {COPY COPY ROLL UP TEST Range 2}, [Account No.]@row, {COPY COPY ROLL UP TEST Range 1}, KAE@row))))

Using the roll up sheet created column ({COPY COPY ROLL UP TEST Range 3}) I am getting a blank cell even with a date being entered the first time.

Leibel S

Adam,

You should be able to simplify your system and remove any need for copying rows etc.

The input sheet where the end user enters the activity should have the following columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
3. Account No.
4. Activity: Dropdown of activities
5. Date

Your end sheet that summarizes all the data would lookup each activity column using the below formula format. This will pull the last entry of this activity, regardless of the date.

=INDEX({Date of activity range},MAX(COLLECT({Row # column},{account activity range},[Account No.]@row,{activity name range}, "Name of activity"))

For the last activity Date:

=max(activitycolumn1:activitycolumn5)

For the last activity Type:

=INDEX({activity name range},MAX(COLLECT({Row # column},{account activity range},[Account No.]@row,{activity date range}, [last activity date]@row))

Let me know if you have trouble

'

Adam Kinney

I am still having trouble. May be mixing up the references on my end I see "{ }" as a reference to a different sheet, and "[ ]" to the current sheet. Happy to share my sheets as neither have anything confidential if that would help.

Leibel S

https://community.smartsheet.com/discussion/comment/269472#Comment_269472

Yes "{xxxxx}" means a cross sheet reference.

My suggestion is to set up a 'collection sheet' (call it as you wish) with the 5 columns I outlined above. End users will keep on adding activities as they happen and can add an additional new line to correct the previous activity.

You sheet with the account # / Opportunity / city / state etc. will lookup this 'collection sheet' using the formulas below. rename the ranges as per whatever you will name them...

This formula will lookup the last entered date for an activity:

=INDEX({collection sheet Date of activity range},MAX(COLLECT({collection sheet Row # column},{collection sheet account activity range},[Account No.]@row,{collection sheet activity name range}, "NAME OF ACTIVITY"))

For the last activity Date use the MAX formula to pull the largest date from within your activity columns:

=MAX(activitycolumn1:activitycolumn5)

For the last activity Type:

=INDEX({collection sheet activity name range},MAX(COLLECT({collection sheet Row # column},{collection sheet account activity range},[Account No.]@row,{collection sheet Date of activity range}, [last activity date]@row))

Adam Kinney

@Leibel S I have no idea what I am screwing up. So sorry for the back and forth!

My end user sheet where they enter a [Last Activity Date] and [Last Activity Type] and adds a new row to a separate sheet through workflow is still pulling the first entered date.

Formula for the cell in pink is: (notice cell in red should have changed it)

=INDEX({3rd COPY ROLL-UP Range 1}, MAX(COLLECT({3rd COPY ROLL-UP Range 2}, {3rd COPY ROLL-UP Range 3}, [Account No.]@row, {3rd COPY ROLL-UP Range 4}, "Initial Presentation")))

Here are the ranges being referenced in the formula on the collection sheet "3rd COPY ROLL-UP":

Date I need in the pink cell of the end users sheet is highlighted in purple on the collection sheet.

The formula in the far right column of the collection sheet is: =IF(AND([Last Activity Type]@row = "Initial Presentation", ISDATE([Last Activity Date]@row)), [Last Activity Date]@row, 0

Leibel S

Did you set up the below fields?

Range #2 should be on the ROW# column.

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)