2

I have a two part question.

1) I would like to create a table that auto-populates a new row that includes a sequential ID number (which includes text), whenever a previous row is filled out. For example:
Table Example

In the example, "19-NBCC-0162" (in the "Tracking Number" column (column A) of row 510) is the ID number I would like to auto-populate when I enter data into the row above it (row 509). I with thinking the new tracking number would appear when data is entered into the "Office" or "Date assigned" columns (C and F) of row 509.

2)The formula I have assigned to the "Tracking number" column cells, in order to include text with sequential number formatting, is:

="19-NBCC" & TEXT(ROW(A161),"-0000")

At the moment, I am not sure if this code would work when combined with the auto-population of a new row concept. If there is a better way to create sequential number formatting with text, I would love to know. And if the two issues can work together, where the Tracking number is auto-populated even better!

Improve this question
5
  • I should mention that all the cells in rows 509, aside from the Tracking Number cell, have formulas that should also be included in the auto-populated row (row 510).
    – jeepin_gypsy
    Mar 28, 2019 at 13:45
  • so basically, you have formulas in entire row?
    – ZygD
    Mar 28, 2019 at 13:46
  • is there something specific in A161? does this value change in every row?
    – ZygD
    Mar 28, 2019 at 13:48
  • ZygD, actually they are drop-down choices, linked to another worksheet. But there is the possibility of including a column with a formula in the future.
    – jeepin_gypsy
    Mar 28, 2019 at 13:48
  • ZygD, no, there is nothing specific about "A161". 161 just happens to be the last number in the list of current tracking numbers we have, so I altered the code - that was suggested on this site. [link(]support.office.com/en-us/article/…) I'm not sure what "A161" actually represents. I thought it would be a cell reference, but it doesn't act that way.
    – jeepin_gypsy
    Mar 28, 2019 at 13:59

1 Answer 1

Reset to default
1

If I have understood it correctly, in the bottom-most row you want to always have the next number populated, together with other empty cells (only having a dropdown lists each).

Dropdowns on cells can be achieved by copy pasting them down. No values selected, means they remain empty until someone selects a value.

Now, your main formula in the "Tracking number" column. For example, if there is something written in the column "Office" previous row 509, we enter the number. Otherwise, leave it with empty string "".

=IF(F509<>"","19-NBCC" & TEXT(ROW(A161),"-0000"),"")

I would fill this formula in entire column.

Improve this answer
3
  • Well, ZygD, that hit the spot! Works like a charm! Thank you, and you have increased my understanding of excel formulas.
    – jeepin_gypsy
    Mar 28, 2019 at 14:18
  • I believe I just put you on the right path an you then fixed things yourself :) Because I have changed this formula a few times while it was already posted... Happy to help!
    – ZygD
    Mar 28, 2019 at 14:21
  • I know, I was trying your initial suggestion and altered it to what was your last suggestion, but noticed the formula kept changing. LOL. Thanks again.
    – jeepin_gypsy
    Mar 28, 2019 at 14:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .