Adding a column in Excel to behave the same as an Access autonumber field

[Bob Sawyer]

Hi

One of my colleagues has asked if it's possible to add a column in Excel which increments a number every time a value is entered in another column.

In other words, he wants the Excel equivalent of an autonumber field in Access.

The number would be unique, and 'permanent' in the sense that if someone inserted a line above, the number on the existing line would need to stay the same, so it was always associated with the same record (line).

To make things more of a challenge, he wants to keep the excel file as xlsx, and share it with others, so I can't dive into a VBA solution, it has to be a formula.

I told him I didn't think it was possible to achieve this with just lookups and formulae, but I thought I'd post in case anyone has any ideas?

I've found reference to the Sequence function while googling this issue, which seems to be what we need, but I'm limited to using Excel 2013 - as Sequence is only in later versions of Excel, I can't use it.

Thanks for reading!

 

[Joe4]

The issue is that you want to automate something without using Excel's main tool for automation - VBA.

And the issue is that formulas are always formulas (and can re-calculate) unless you convert them to hard-coded values, either via VBA or manually (which does not sound like you want to do either of those two things).

The only way I can think of is to use a DateTime Stamp. There is one clever approach that I have seen that does not require VBA. Perhaps it will work for your needs.
See here: How to Quickly Insert Date and Timestamp in Excel

I don't think I have ever seen any other way to do something like this without VBA.

 

[Bob Sawyer]

Hi Joe4

Thanks for posting. I agree those are the issues - there's no way I've found to turn off a re-calculate of a formula without turning off autocalc for everything. Similarly, there's no way I can see to have the formula calculate once, but then once it has a value, don't do it again. It's just fundamentally how formulae work!

Your link is interesting though - I'll look into this. Maybe I can format the date as a number - that might work.

 

[Bob Sawyer]

Well, I've done some testing and this looks promising. The only snag is the need to enable iterative calculation options.

Unfortunately this sheet needs to be shared with a several people who are not very Excel-literate, so they'll probably forget to turn off the option again and end up getting confused. That's kind of the reason we were trying to avoid using a VBA solution.

However, this is the closest thing to a solution I've seen and I don't think there's going to be a better one - so I'll mark this as solved.

Thanks for your help.

 

[Joe4]

Unfortunately this sheet needs to be shared with a several people who are not very Excel-literate, so they'll probably forget to turn off the option again and end up getting confused. That's kind of the reason we were trying to avoid using a VBA solution.

I am not 100% certain, but I didn't think that you needed to set it each time.
I think if you toggle that setting, and save the workbook, it should save that setting for that workbook.

Have you tried changing the setting, saving the workbook, and then re-opening it and seeing if that setting "stuck"?

 

[Bob Sawyer]

Well - you are quite right!

I just assumed that was a general option that would 'stick' and affect any excel files opened, but it does indeed only apply to the specific workbook.

That turns things around and I think this solution has just become viable again!

I'll look into it some more - thanks and have a good weekend!

 

[Joe4]

You are welcome.
Glad I was able to help!

Yes, many of those settings are Workbook-specific.