One recurring question that I come across in Excel is how to have a reference or label that automatically increments as more data is added. You know the sort of thing, you have a sheet that is used by multiple people to track something, for example requests coming in via a paper process (although see the blog about Microsoft Forms to do away with these!) that you want to have a consistent reference format for and make sure you don’t have different people entering different things or duplicating codes.
Well the good news is that it is fairly straightforward in principle. The bad news is that there are some gotchas to watch out for that can break the functionality if not careful which can always be a particular issue with shared spreadsheets.
Take a simple tracker format as follows:
Here we have a reference field at the start which is meant to be unique and increment by one. If the form is to track widgets we may want a reference that starts with WIDG and then a unique number. Depending on the number of forms we think we might be tracking we may also want to pad the number so that instead of
WIDG1
we get
WIDG001
Obviously if we were planning on more than 999 entries we need to pad more. Or consider whether Excel is the appropriate way to track these!
To start with create your fields that you want to record starting with a reference column, then any other columns to capture the information relevant to your needs.
Once you have it, use the Format as Table option to create a table that will be the main part of the sheet. The reason for this step is that tables will automatically inherit formula, formatting and data validation rules when a new row is added; in this case the reference will automatically flow through when a new row is added.
The simple bit is to enter a formula as follows to our reference column. It doesn’t have to be the first column but this probably makes most sense:
=”WIDG”&TEXT(ROW()-1,”000”)
To break this down
“WIDG” is our prefix and is text so we put it in “”
& is the Excel operator for adding text snippets together, or in this case the output from a formula converted to text.
The next part is to get the number and as we are dealing with text we use TEXT to convert it
ROW()
returns the current row and we are using a magic number of -1 offset it. This is because our headers in our table start at row 2. Obviously here is gotcha number one. If rows are added or deleted above the table this number will no longer be correct.
The “000” tells the TEXT formula what format should be used on the text, in this case it will pad the output of ROW with three 0s.
So, now we have that we can enter some data and when we hit tab to add another row the formula will automatically be copied and as the offset of the row has gone up, so will the number.
Easy.
Except.
Because we are using a formula that uses a fixed offset, if someone performs a sort on any of the other fields it will cause the formula to update which will mess up the whole show. Now, we could simply tell people not to perform a sort, after all this is a tracker and if people want to find things they should really be searching or filtering but we can also “fix” the existing entries by using a little trick.
If we select those rows that have data but still have the formula in the reference fields and then:
Copy → Paste Special→Values
We will replace the formula with the values which will now fix the reference. Only a blank line would be subject to the problem of sorting.
This method will prevent duplicates by automating the reference process, but someone could potentially still attempt to overwrite the reference so as a belt and braces we can prevent duplicates using an old technique as follows:
Select the column you want to protect, in this case it is A starting at A2 down to the final entry. Then select Data Validation
In the validation box select Custom and then in the Formula box enter the following:
=COUNTIF($A:$A, A4)<2
This will now prevent duplicate entries being added to that column.
So that’s it, a simple way to create a reference code that automatically increments.