location and date count

Kyle Black

Alrighty

Need some help with a formula that will search a location and division for specifics and then look at the newest date from that location and division and give me a count since that date. the first two columns are static but the data sheet is updated by forms weekly. this is a shot of the metric sheet for the dashboard. i need it to find the most recent recordable date and give me a count on how many days it has been.

Paul Newcome

Ok. You are going to want to start with a MAX/COLLECT to pull the most recent date for your location/division combo.

=MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Location Column}, @cell = Location@row, {Other Sheet Division Column}, @cell = Division@row))

Once we subtract that from TODAY(), we will know how many days it has been since the last submission for that particular location/division.

=TODAY() - MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Location Column}, @cell = Location@row, {Other Sheet Division Column}, @cell = Division@row))

Paul Newcome

Are you able to provide a screenshot of at least the column names on the source sheet as well?

Kyle Black

Paul Newcome

Are forms populated at the top or the bottom of the sheet?

Kyle Black

bottom of the sheet

Paul Newcome

Ok. You are going to want to start with a MAX/COLLECT to pull the most recent date for your location/division combo.

=MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Location Column}, @cell = Location@row, {Other Sheet Division Column}, @cell = Division@row))

Once we subtract that from TODAY(), we will know how many days it has been since the last submission for that particular location/division.

=TODAY() - MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Location Column}, @cell = Location@row, {Other Sheet Division Column}, @cell = Division@row))