Excel: increment numbers in reference

Hello Experts,

I have a column named: image_ref and I have a list of references folders, what I want to achieve is this :

I have like 50 references and would like to have them in order automatically without typing one by one.

The first reference starts with AJ001
the next should be AJ001 and third AJ003, so you guessed the rest, right? :)

How to that automatically with Excel?

Thank you
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Select the first reference. Hover the mouse over the lower right-hand corner until it's a plus sign and drag down.
Martin LissOlder than dirtCommented:
Or after you have at least one record you can add this code to the sheet. It assumes you change something in column "A" and when you do you want column "B" for that row to be one more than the previous value in column "B".

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns("A")) Is Nothing Then
    Target.Offset(0, 1) = "AJ" & Format(Right(Target.Offset(-1, 1), 3) + 1, "000")
End If

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thebest8Author Commented:
Wow dear Martin, that was confusing, forgive me for my basic knowledge, so where to put that code? or how to use it?
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Martin LissOlder than dirtCommented:
Sorry about that. I've attached a sample workbook that contains a slightly modified and documented version of the code. If you go to Visual Basic via Alt+f11 and you double-click on Sheet1 in the Project - VBA Project window you'll see the code.
thebest8Author Commented:
So this involves VisualBasic, I don't have it installed and I don't use it at all...

Let me ask you this, how can I do this through a web script? possible?
Martin LissOlder than dirtCommented:
No. It involves Visual Basic for Applications, better known as VBA. VBA is built into Excel and all other Office applications including Excel.
thebest8Author Commented:
Thank you very much dear Martin, I will try to achieve it with a php loop then copy/paste it. thanks alot
thebest8Author Commented:
thank you
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
thebest8Author Commented:
Ok, thank you, this is how I achieved it:


for($counter = 1; $counter <= 50; $counter++)

			print "AJ0" . "$counter" . "<br>";

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.