Row numbers

Amit Wadhwani

Hi,

I have seen multiple threads around this but still I'm surprised there is no dynamic solution for this, I have tried auto populating ROWID by AutoNumber column and as you can see in screenshot whenever I try to add a new row in between it calculates the row number from the bottom (WOW), this is very bad and this is causing my notification to sent incorrectly.

I tried =RowID1 + 1 which autopopulates the rows ID, but when I insert a new row in between two rows it doesn't work.

Is there any formula that can help with auto populating row IDs? even when we insert a new row in between two rows?

Paul Newcome

Try this in a text/number column...

=COUNTIFS(Task$1:Task@row, OR(@cell = "", @cell <> ""))

Paul Newcome

Try this in a text/number column...

=COUNTIFS(Task$1:Task@row, OR(@cell = "", @cell <> ""))

Amit Wadhwani

That's awesome Paul!!

Thank you very much :)

Paul Newcome

Happy to help! 👍️

Mehran Bagherian

Hi Paul,

Can you explain this formula? how does it work.

I couldn't use it. it doesn't work for me.

Paul Newcome

@Mehran You would put the formula in row 1 of a text/number column and then dragfill down the rest of the rows.

Mehran Bagherian

Paul,

I did it but it does not work. it gives me #UNPARSEABLE error.

I changed =COUNTIFS(Task$1:Task@row, OR(@cell = "", @cell <> "")) to

=COUNTIFS(Task Name$1:Task Name@row, OR(@cell = "", @cell <> ""))

as my column is "Task Name". Any other changes needed on this formula?

Paul Newcome

@Mehran When you are referencing a column that has spaces, numbers, and/or special characters in the name, you need to wrap it in square brackets.

=COUNTIFS([Task Name]$1:[Task Name]@row, OR(@cell = "", @cell <> ""))

Mehran Bagherian

Paul,

I solved it. I just used another column instead of "Task Name". I wonder why it was not working with that column!!

Thank you.

Sterling Crawford

If all you want is for the ROWID to match the row number I like this formula, which works as long as you point it at a column that has unique values (e.g. an autonumber column). Based on your screenshot I'm using the 'Task' column at the column with unique values:

=MATCH(Task@row, Task:Task, 0)

Now that Smartsheet has Column Formulas I like this one best because it can be used as a column formula, whereas anything that references a specific row can't be. It will also auto-update as you move rows up and down.

Paul Newcome

@Sterling Crawford Yes. Having the ability to use column formulas certainly makes things much easier and reliable.

I have switched from the previous method (pre-column formulas) to using an auto-number column and the MATCH function in a text/number column pointing at the auto-number.

Niall M

@Sterling Crawford this is great! I used the auto number system/column for the unique number to avoid duplication.

I have been down a wormhole trying to find a scalable way to implement this:

https://community.smartsheet.com/discussion/75593/formula-to-indicate-a-task-is-ready-because-predecessors-are-complete-solution

Kevin Mahaffey

@Paul Newcome Huge help, thank you.

Kevin Mahaffey

@Paul Newcome - Hi,

Hoping you may be able to help adjust the formula below so that it does not include rows when "closed" is selected. In effect only counting rows with "open" or "backlog" selected.

=COUNTIFS([Project Name]$1:[Project Name]@row, OR(@cell = "", @cell <> ""))

Greg Arnson

There is a problem when using the auto number type column with this formula - if you copy rows from another sheet, it keeps the auto number from the original sheet which can cause duplicate auto numbers (an therefore create errors when using this column reference in formulas). Anyone aware of a solution or workaround for this to ensure unique auto numbers (or to ensure accurate row ID references)?

veronica.cortes55311

Yes @Greg Arnson this is where it failed me, it was almost perfect!

Now I'll have to update the sheet as rows are added since I cannot make this a column formula.

Will it work if you create a workflow to change the value of the cell everytime a row is added?