Thanks:  0
Likes:  0

1. ## Drag Text/Number and add 1

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,

2. ## Re: Drag Text/Number and add 1

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!

3. ## Re: Drag Text/Number and add 1

SORRY

How glaringly obvious!!

I assumed because text was involved that wouldn't work!!

4. ## Re: Drag Text/Number and add 1

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.

5. ## Re: Drag Text/Number and add 1

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.

6. ## Re: Drag Text/Number and add 1

OK chaps, I've recovered after thoroughly embarrassing myself with the first part of the question (there must be a lesson there somewhere), can I go back to the second part:

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D4 =

A
B
C
D
1
Date O/NoQtyEtc
2
12/01/2004ASC/2558
3
12/01/2004ASC/2559
4
12/01/2004ASC/2560
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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

[/b]

7. ## Re: Drag Text/Number and add 1

Originally Posted by atmospheric
....
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

[/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.

8. ## Re: Drag Text/Number and add 1

Sorry Yogi, I meant A5 and B5 of course

9. ## Re: Drag Text/Number and add 1

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 ...

******** ******************** ************************************************************************>
 Microsoft Excel - y040118h1a.xls ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B5B6 =

A
B
C
D
1
DateO/NoQtyEtc
2
12/01/2004ASC/2558
3
12/01/2004ASC/2559
4
12/01/2004ASC/2560
5
12/01/2004ASC/2561
6

 Sheet13

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Is this what you are looking for?

10. ## Re: Drag Text/Number and add 1

Originally Posted by atmospheric
OK chaps, I've recovered after thoroughly embarrassing myself with the first part of the question (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

Maybe a set up like:

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B4B5B6B7B8B9 =

A
B
C
D
1
ASC/
2
2558
3
DateO/No
4
1/17/2004ASC/2558
5
1/18/2004ASC/2559
6
1/20/2004ASC/2560
7
1/26/2004ASC/2561
8
1/31/2004ASC/2562
9
1/31/2004ASC/2563
10
 Sheet1

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

B4:

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•