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
thebest8Asked:
Who is Participating?
 
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

0
 
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.
0
 
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.
29066164.xlsm
0
 
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?
0
 
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.
0
 
thebest8Author Commented:
Thank you very much dear Martin, I will try to achieve it with a php loop then copy/paste it. thanks alot
0
 
thebest8Author Commented:
thank you
0
 
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
0
 
thebest8Author Commented:
Ok, thank you, this is how I achieved it:

<?php

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

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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.