Formula options for filling in blank value using previous rows

markkrebs
markkrebs ✭✭✭✭✭✭

Data is coming in like this with blank Name gaps:

I'm using this in Name helper

=IF(ISBLANK(Name@row), IF(ISBLANK(INDEX(Name:Name, MATCH([Row ID]@row - 1, [Row ID]:[Row ID], 1))), INDEX(Name:Name, MATCH([Row ID]@row - 2, [Row ID]:[Row ID], 1)), INDEX(Name:Name, MATCH([Row ID]@row - 1, [Row ID]:[Row ID], 1))), Name@row)

and have successfully filled in as you could see up to 2 blanks. E.g. Name 4. To accommodate more than 2 gaps the only way I came up with is to keep repeating the if sequence but that'll be a crazy long formula if I want to accommodate say 8 or 9 gaps.. Looking for a simpler way.....

@Paul Newcome @Genevieve P. ... show me your magic 😁

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/16/23 Answer βœ“

    @markkrebs I've had to solve something similar. Try this:

    =IF(ISBLANK(Name@row), INDEX(Name:Name, MATCH(MAX(COLLECT([Row ID]:[Row ID], [Row ID]:[Row ID], < [Row ID]@row, Name:Name, NOT(ISBLANK(@cell)))), [Row ID]:[Row ID], 0)), Name@row)

    Logic: If the Name is blank, find the Name value from the row with the highest Row ID that is less than this row's Row ID, where the Name isn't blank; otherwise, set this cell to Name@row.

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • markkrebs
    markkrebs ✭✭✭✭✭✭
    Answer βœ“

    Awesome!!! thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/16/23 Answer βœ“

    @markkrebs I've had to solve something similar. Try this:

    =IF(ISBLANK(Name@row), INDEX(Name:Name, MATCH(MAX(COLLECT([Row ID]:[Row ID], [Row ID]:[Row ID], < [Row ID]@row, Name:Name, NOT(ISBLANK(@cell)))), [Row ID]:[Row ID], 0)), Name@row)

    Logic: If the Name is blank, find the Name value from the row with the highest Row ID that is less than this row's Row ID, where the Name isn't blank; otherwise, set this cell to Name@row.

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • markkrebs
    markkrebs ✭✭✭✭✭✭
    Answer βœ“

    Awesome!!! thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!