Row numbers
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?
Best Regards
Amit Wadhwani, Smartsheet Community Champion
Smartsheet CoE, Ignatiuz, Inc., Exton, PA
Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/
Best Answer
-
Try this in a text/number column...
=COUNTIFS(Task$1:Task@row, OR(@cell = "", @cell <> ""))
Answers
-
Try this in a text/number column...
=COUNTIFS(Task$1:Task@row, OR(@cell = "", @cell <> ""))
-
That's awesome Paul!!
Thank you very much :)
Best Regards
Amit Wadhwani, Smartsheet Community Champion
Smartsheet CoE, Ignatiuz, Inc., Exton, PA
Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/ -
Happy to help! 👍️
-
Hi Paul,
Can you explain this formula? how does it work.
I couldn't use it. it doesn't work for me.
-
@Mehran You would put the formula in row 1 of a text/number column and then dragfill down the rest of the rows.
-
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?
-
@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 <> ""))
-
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.
-
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.
-
@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.
-
@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:
-
@Paul Newcome Huge help, thank you.
-
@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 <> ""))
-
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)?
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!