Autonumber Column

[Legacy 436357]

Hello,

I have a Customer table that I would like the first column to autonumber to the next higher number.

When sorting the table can that number remain with the Customer?

What I have tried so far doesn't keep that ID with the Customer when sorting the table columns.

Thank you

 

[Joe4]

If you are just using native Excel formulas, it will probably not work the way you need.
You will probably need to hard-code the value, which will require VBA (if you do not want to do it manually).

If you need help creating that VBA code, please provide the following information:
- In which columns to these numbers occur?
- Are they straight numbers, or is there also some text mixed in with them?
- Should it always look for the maximum number currently in the column, and add one to it for the next number?
- When should a new number be added/created? (Do you want to run something manually, or have it triggered to happen automatically upon some event happening? If the later, what is the event that should trigger it to run?)

 

[Legacy 436357]

Thanks Joe I attached here what I have in mind. Can this be done to autonumber the IDs for each Customer so when I sort any of the columns that ID remains with the Customer?

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
2	ID	Company Name	Contact Name	Billing Address	City	State or Province	Postal Code	Phone Number	E-Mail Address	Notes
3	101
4	102
5	103
Sheet1

 

[Joe4]

How exactly are new customers being added?
Are they being imported, copied in from somewhere, or is someone manually keying them?
If they are being manually entered, will the Company Name ALWAYS be filled out?
My thinking is to have code automatically run when Company Name is populated to populate the ID column.

 

[Legacy 436357]

I figured it out without using VBA thanks anyway.

 

[Joe4]

I figured it out without using VBA thanks anyway.

Please post how you did it, so it may help future people with the same question (and I am curious how you did it too).

 

[Legacy 436357]

In cell B3 =LEFT(C3,3)&RIGHT(I3,4)

This assigns the first 3 digits of company name and last 4 digits of phone number.

The columns sorted always keep this ID with the Customer.

 

[Joe4]

Ah, okay. So you switched from using an Autonumber to using a field calculated from some of the record details.
Just note that if company name or phone number switches, so will you ID number (I deal with a lot of client stuff, and compnay name changes are not uncommon).
So if you need the ID to be static and never changing, that probably won't work for you.

 

[Legacy 436357]

It is not likely the case in my situation. If the company name changes or the phone number it will still be applied to any past records for referencing.

 

[Joe4]

OK. As long as it works for you. Just something to be aware of.