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

Options

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.

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Options

    @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

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!