Drag Text/Number and add 1

atmospheric

Well-known Member
Joined
Jul 16, 2002
Messages
565
I have an Order Number that takes the form ASC/2558.

How can I drag it down about 500 cells and have the last 4 digits increment by 1 in each cell?

e.g, in column B

B1 = ASC/2558
B2 = ASC/2559
B3 = ASC/2560
etc

Or perhaps a better solution for me would be that if there was an entry in A2, make B2 one greater than B1

Hope that makes sense,

Thanks in advance
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
highlight the first 3 cells that have:

B1 = ASC/2558
B2 = ASC/2559
B3 = ASC/2560


click on the square in the bottom right of the selection (your cursor will turn to a +) and drag em down!
 
Upvote 0
Or select one cell, right click on the little box in the lower right corner, drag down and select Fill Series from the menu that appears.
 
Upvote 0
Or continuing with Tod (tbardoni's) thinking --

let us say B1 houses ASC/2558, then let us select cell B1 and then drag it down as far down as necessary.
 
Upvote 0
OK chaps, I've recovered after thoroughly embarrassing myself with the first part of the question :oops: (there must be a lesson there somewhere), can I go back to the second part:
Book1
ABCD
1DateO/NoQtyEtc
212/01/2004ASC/2558
312/01/2004ASC/2559
412/01/2004ASC/2560
Sheet1


In column B I could do with a formula that adds one to the O/No IF there is an entry in column A of the same row.

So if there is a date in A4 for example, B4 would read ASC/2561

Thanks again in advance, and please forgive me my sins :oops:
[/b]
 
Upvote 0
atmospheric said:
....
In column B I could do with a formula that adds one to the O/No IF there is an entry in column A of the same row.

So if there is a date in A4 for example, B4 would read ASC/2561

Thanks again in advance, and please forgive me my sins :oops:
[/b]
Hi atmospheric:

Need some clarification -- per your illustration, there is already an entry in cell B4 ... ASC/2560

So, you are saying if there is a date in cell A4, the last 4 digits of entry in cell B4 should be incremented by 1 -- you can not do that with a formula in cell B4. For a formula based approach, you may need to insert a column to the right of B4 and then use a formula, and if you can not add a column , you will need VBA to do what you are trying to do.
 
Upvote 0
Hi atmospheric:

Don't be shy on details. So, what we have so far is that if cell A5 has a date in it, B5 should get the entry in cell B4 with the last 4 digits incremented by 1 -- now what should happen if there is no date entry in cell A5?

In the following I have assumed that you want the entry in B5 to be blank if there is no date entry in cell A5 ...
y040118h1a.xls
ABCD
1DateO/NoQtyEtc
212/01/2004ASC/2558
312/01/2004ASC/2559
412/01/2004ASC/2560
512/01/2004ASC/2561
6 
Sheet13


Is this what you are looking for?
 
Upvote 0
atmospheric said:
OK chaps, I've recovered after thoroughly embarrassing myself with the first part of the question :oops: (there must be a lesson there somewhere), can I go back to the second part:

...

In column B I could do with a formula that adds one to the O/No IF there is an entry in column A of the same row.

So if there is a date in A4 for example, B4 would read ASC/2561

Thanks again in advance, and please forgive me my sins :oops:

Maybe a set up like:
Book1
ABCD
1ASC/
22558
3DateO/No
41/17/2004ASC/2558
51/18/2004ASC/2559
61/20/2004ASC/2560
71/26/2004ASC/2561
81/31/2004ASC/2562
91/31/2004ASC/2563
10
Sheet1


B4:

=IF(N(A4),$B$1&$B$2+COUNT($A$3:A3),"")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top