How do I automatically populate a summary row at top of sheet with latest non-blank information

ScullyAG

I have a sheet with a Release Date and then four additional columns, each column representing a different piece of software. I want a user to be able to add a new row and only populate the Release date and the new version of software they have released. I want the top row to automatically populate the latest, non-blank data from each column. I can easily determine the row number for the last populated cell, but cannot get that to populate in the first row, which is just a quick snapshot to advise here is the latest release for each software. How do I do that?

For example:

Release Date APP1 APP2 APP3 APP4

Row 1: =TODAY() V3 V2 V1 V1

Row 2:

Row 3: 9/15/20 V1 V1

Row 4: 9/20/20 V2 V1 V1

Row 5: 9/30/20 V3 V2 V1 V1

I feel like I've over-complicated it, so I'm reaching out here.

Ramzi K

@ScullyAG

Here's one way to do it. I would use the Sheet Summary and not the first row in the sheet to avoid circular references with these formulas.

The sheet would look like this:

The Sheet Summary section would have the fields with your info for each app:

The formulas for the sheet summary fields are as follows (identical except for APPn column reference):

APP1:

=MAX(COLLECT([Release Date]:[Release Date], [APP1]:[APP1], <>"")) + " - " + INDEX([APP1]:[APP1], MATCH(MAX(COLLECT([Release Date]:[Release Date], [APP1]:[APP1], <>"")), [Release Date]:[Release Date]))

APP2:

=MAX(COLLECT([Release Date]:[Release Date], [APP2]:[APP2], <>"")) + " - " + INDEX([APP2]:[APP2], MATCH(MAX(COLLECT([Release Date]:[Release Date], [APP2]:[APP2], <>"")), [Release Date]:[Release Date]))

APP3:

=MAX(COLLECT([Release Date]:[Release Date], [APP2]:[APP2], <>"")) + " - " + INDEX([APP2]:[APP2], MATCH(MAX(COLLECT([Release Date]:[Release Date], [APP2]:[APP2], <>"")), [Release Date]:[Release Date]))

APP4:

=MAX(COLLECT([Release Date]:[Release Date], [APP4]:[APP4], <>"")) + " - " + INDEX([APP4]:[APP4], MATCH(MAX(COLLECT([Release Date]:[Release Date], [APP4]:[APP4], <>"")), [Release Date]:[Release Date]))

I hope this helps you.

Cheers,

Ramzi