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?