Adjusting formula for created Row# to not begin from 1 e.g. begin with 88, then 89, 90... etc.

Options

Hi,

For context I've created a report that draws from 4 sheets as a programme level plan. I encountered an issue with the predecessors not reporting clearly from source sheet. For those who haven't encountered this issue, smartsheet uses the row# to assign predecessors, hence with multiple source sheets only the amount of predecessors are shown and not the specific row#.

Using the link below I managed to use the formulas discussed to solve this issue.

The issue I have now is that the predecessors can't be linked between sheets at report level as tasks begin from Row 1 in each sheet an hence predecessors are assigned to individual sheet. A solution to this can be if there is a way to adjust the Row# to begin with the proceeding Row# from the previous sheet e.g. 88. Formula for Row# used: =MATCH([Task Name]@row, [Task Name]:[Task Name], 0)

Tags:

Best Answer

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓
    Options

    If you just need to reference the last row #, it could be something simple like:

    "Adjusted Row #" =MAX({Row # Range})+[Row #]@row

    Where {Row # Range} references the previous sheets "Row #" column. For my example below, Row ID is the system column, Row # =MATCH([Row ID]@row, [Row ID]:[Row ID], 0), and then the "Adjusted Row #" is where you would put ^that formula (I didn't bother referencing another range, so replace 88 in my example below with your previous sheet's range)

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓
    Options

    If you just need to reference the last row #, it could be something simple like:

    "Adjusted Row #" =MAX({Row # Range})+[Row #]@row

    Where {Row # Range} references the previous sheets "Row #" column. For my example below, Row ID is the system column, Row # =MATCH([Row ID]@row, [Row ID]:[Row ID], 0), and then the "Adjusted Row #" is where you would put ^that formula (I didn't bother referencing another range, so replace 88 in my example below with your previous sheet's range)

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • adimunir
    Options

    That's brilliant Jason, thanks!

    Do you know how I would adjust the formula for the pred#1, pred#2 etc. to accommodate for the inclusion of the adjusted row# rather than original row #.

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Options

    That you wouldn't be able to do, by default the predecessors are looking at the system row. You unfortunately wouldn't be able to point it at a custom column.

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!