How Do I Capture The Most Recent Date From One Sheet Onto Another Sheet?

Brittaney Pizzato

Hello!

I am trying to pull the most recent date for a specific project into a cell on another sheet and have it update automatically when a new/most recent date is put in.

Ie. The 10/27/23 date is pulled to a master sheet and updates automatically when a more recent date is put in.

I was trying to do a bunch of combinations using MAX/COLLECT, with no luck.

Thanks!

Lucas Rayala

Hi @Brittaney Pizzato, MAX COLLECT should work in this instance -- I doublechecked on a page, using dates at the collection target.

=MAX(COLLECT([Date]:[Date], [Project Name]:[Project Name], 5001))

Two things that could go wrong with the above formula. First, make sure the column your formula's cell is in is a "date" column. If you can't do that, then add this to the end:

+ ""

That will convert the date to a string (basically a word).

The second potential issue is that the Project Name is a number, and you are using a string to search, or it's a string and you are using a number. Basically, if the project name is the number 5001, and you are using "5001" (with quotes) as your criteria, then the formula thinks you are searching for the word 5001, not the number. To search for the number, you would need to remove the quote from 5001. Basically, make sure your data-types are the same. Sometimes that can get tricky and you may need a helper column to convert the information in a column to the correct data type to reference.

Brittaney Pizzato

Hi Lucas,

Thanks for your help. I changed our Project name to show numbers and a name. See below.

To pull the most recent date into a single cell on another sheet I used:

=MAX(COLLECT({Date}, {Project Name}, 5001 - Migration to OCI))

Sheet 1:

It didn't work. Maybe I'm still missing something? I went to my Sheet 1 and pulled the Date column first, then the Project Name column. I also changed the Column Type to Date on my Sheet 2.

Sheet 2:

Thanks!