Autopopulate a value in the cell of a column, based on previous values
Hi,
Appreciate your help! I have a sheet, where Autonumber is taken up for the first column. I have a Priority Column, which I want to autopopulate based on if the number already exists.
Item #  Item Requested  Priority
21  Test 1  1
23  Test 2  2
24  Test  3
25  Test  here the number should autopopulate as '4', because 1 to 3 are taken
Please let me know if there is a way to do this?
Thank you,
Deepthi
Answers

Hi Deepthi,
I'm not sure I understand what you're looking to do... are you just wanting a second column that numbers rows, in the same way that the AutoNumber does?
If so, you could set up a formula to look at the cell above and +1. You would need to have three rows of data (the top one with 1, then two rows below with the formula) before it will autopopulate. See here:
Is that what you meant? Let me know if this works for you. You may want to Lock the column as well, so no one deletes the formula.
Cheers!
Genevieve

Hi Genevieve,
Thank you for your response. What you have provided looks like it'll work, but it is not automated, everytime, I'll need to add formula: =Number9+1, or =Number10+1 into the cell.
Is there a way, this can be transformed to take the previous cell value and add 1, without having to write out the formula. I see your formula helps, if the preceeding value changes then automatically all cell values below changes.
Thank you,
Deepthi

Hi Deepthi,
Since we haven't locked in the row number with an absolute value (see here), this means that it's dynamic and will update to the next number every time it's autofilled (see here).
For example, the way I created the sheet above is that I started on row 6 and had this formula:
=Number5 + 1
Then I used the dragfill function to bring that formula down through the column (see here). As I dragged the formula into the row below, the Number5 update to Number6 for row 7. Does that make sense? This same autofill will happen when new rows are added. The easiest way to explain it would be if you tested to see what I mean!
Let me know if that helps clarify how the formula works.
Cheers!
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!