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

Find more posts tagged with

Formulas

Support

Solution Center

Accepted answers

All comments

Genevieve P.

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:

Screen Shot 2020-07-01 at 7.03.30 PM.png

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

dsmartsheetuser

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

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.