Auto-populate a value in the cell of a column, based on previous values
Hi,
Appreciate your help! I have a sheet, where Auto-number is taken up for the first column. I have a Priority Column, which I want to auto-populate 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 auto-populate 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 Auto-Number 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 auto-populate. 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 auto-filled (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 drag-fill 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 auto-fill 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
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!