Formula (IF Statement?) that checks if row numbered sequentially?
Can I write a formula that could check if rows belonging to same project number have been numbered sequentially? My sheet tracks several projects and each project could have several entries on one sheet and these must be numbered so we know how many submissions were made per project.
I wrote one to check for duplicates with a check column and then used conditional formatting to highlight duplicates.
However now, I need to check that rows being submitted into my sheet have been numbered appropriately, with no skipped numbers and that they are in fact being numbered sequentially. The person submitting will have to indicate if this is their 1st submission, 2nd, 3rd etc for a particular project in a specific dedicated column. So I assumed some sort f If statement that would check this column if Project column is the same, but then get lost on how to write the criterion.
I assumed another check type formula could do the trick but I cannot seem to figure it out. Is this possible?
Best Answers
-
@Susan Vieira Here is my formula rewritten with your column names from your latest screenshot:
=IF([Amendment #]@row <> MAX(COLLECT([Amendment #]$1:[Amendment #]1, [Project #]$1:[Project #]1, [Project #]@row)) + 1, 1)
Edited to correct a column name I had missed when updating.
-
Correct. That is why I mentioned to start the formula in row 2. SO that it would look at the current row but only compare to the previous rows. I am glad it's working for you now. ποΈ
Answers
-
It sounds like you need a helper column with a drop down.
=if(projnum2 = projnum1+1,1,0)
You can also automatically create the project numbers with an automatic column so it will assign the numbers on creation of a new row
-
It's a bit more complicated than your example as I need to compare 2 columns and/or cells.
We cannot automatically number them as the sheet will have several projects with their own submission numbers. It is possible to have several submission 1's depending on how many projects get entered. Project X will have some submissions and those submissions will be numbered and must be sequential and then all submission numbers for Project Y need to be sequential, etc etc.
So I need it to find all rows with Project X and then cross check that the numbers in Submission # column are sequential. Am I making any sense?
So how can I adapt the above formula that checks Submission numbers for a specific project only are sequential? It involves 2 columns Project Number and Submission number.
Thanks again!
-
Couldn't you automate the submission numbers by starting in row 2 and using something along the lines of...
=MAX(COLLECT([Submission #]$1:[Submission #]1, [Project Column]$1:[Project Column]1, [Project Column]@row)) + 1
Basically you pull all of the Submission Numbers from above the current submission where the Project matches, grab the highest value, then add 1 to it.
-
If your rows are just numbers, you can use this formula:
=IF([Primary Column]@row = [Primary Column]1 + 1, 1, 0)
This is with a blank row above the 1. If you don't want that blank row, you will need to start your formula in the #2 row as it is referencing the row above it.
If your rows contain the words "1st", "2nd", "3rd" etc, this formula won't work. However, you can use a helper column that will convert the number to the words. You can then hide your column with #'s. Column3 in my screenshot contains the same formula looking at Column2 and it doesn't give an error, but doesn't check the box as it can't differentiate the number sequence, but it does work for the Primary Column...
If you do need to have them as words instead of numbers, use this formula to combine them.....
Let me know if you have any questions.
-
@Paul Newcome , I wish I could but I cannot. The submission numbers are independent and unrelated to Smart Sheet so I cannot have it automatically generated. Thanks so much for your help though!
-
Ok. In that case at least we now have a formula that tells us what the submission number SHOULD be. From there all we would have to do is drop it in an IF statement to say that if the submission number does not equal the output of the above, then flag the row.
=IF([Submission #]@row <> [what it should formula], 1)
=IF([Submission #]@row <> MAX(COLLECT([Submission #]$1:[Submission #]1, [Project Column]$1:[Project Column]1, [Project Column]@row)) + 1, 1)
-
@Leslie Merlino , I do not need to have it written 1st or 2nd, 1 and 2 is fine.
I like your formula but it is not just about the numbers in primary column, it will depend on the project it is linked to.
See photo for example. I want to make sure that The next Project B Amendment # is 4. And if they submit 5, I want something (conditional formatting I assume) to flag that amendment 4 is missing for Project B. Same for A and that the next C will be 2 and not 4.
-
@Susan Vieira Here is my formula rewritten with your column names from your latest screenshot:
=IF([Amendment #]@row <> MAX(COLLECT([Amendment #]$1:[Amendment #]1, [Project #]$1:[Project #]1, [Project #]@row)) + 1, 1)
Edited to correct a column name I had missed when updating.
-
@Paul Newcome Thank you. I feel I am so close but I cannot get it to work/figure it out.
Here is what it looks like:
It is checking everything, even though project C and A are out of sequence. I want Row 8 & 9 to be flagged for being out of sequence. Either with a check or no check, I don't care which.
-
I believe I got it to work!!! See pic with final formula. I needed it to evaluate the range of amendments except for the new row of cells.
Thank you!
-
Correct. That is why I mentioned to start the formula in row 2. SO that it would look at the current row but only compare to the previous rows. I am glad it's working for you now. ποΈ
-
ooops, I missed that comment, it would have have saved me some frustration. Thanks again, so helpful!
-
Help Article Resources
Categories
Check out the Formula Handbook template!