Excel: increment numbers in reference

thebest8
thebest8 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Select the first reference. Hover the mouse over the lower right-hand corner until it's a plus sign and drag down.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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

Author

Commented:
Wow dear Martin, that was confusing, forgive me for my basic knowledge, so where to put that code? or how to use it?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
No. It involves Visual Basic for Applications, better known as VBA. VBA is built into Excel and all other Office applications including Excel.

Author

Commented:
Thank you very much dear Martin, I will try to achieve it with a php loop then copy/paste it. thanks alot

Author

Commented:
thank you
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial