Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Drag Text/Number and add 1

This is a discussion on Drag Text/Number and add 1 within the Excel Questions forums, part of the Question Forums category; I have an Order Number that takes the form ASC/2558. How can I drag it down about 500 cells and ...

  1. #1
    Board Regular atmospheric's Avatar
    Join Date
    Jul 2002
    Location
    Leeds, UK
    Posts
    551

    Default 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,

    Thanks in advance
    I got a sweater for Christmas. I really wanted a screamer or a moaner.

  2. #2
    Board Regular TryingToLearn's Avatar
    Join Date
    Sep 2003
    Location
    NJ
    Posts
    692

    Default 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!
    -Bob [XL2003 & 7 on XP] Slowly coming out of the Stone Age and trying to trade in my abacus.

  3. #3
    Board Regular atmospheric's Avatar
    Join Date
    Jul 2002
    Location
    Leeds, UK
    Posts
    551

    Default Re: Drag Text/Number and add 1

    SORRY

    How glaringly obvious!!

    I assumed because text was involved that wouldn't work!!
    I got a sweater for Christmas. I really wanted a screamer or a moaner.

  4. #4
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

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


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default 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.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    Board Regular atmospheric's Avatar
    Join Date
    Jul 2002
    Location
    Leeds, UK
    Posts
    551

    Default 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
    =

    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

    Thanks again in advance, and please forgive me my sins
    [/b]
    I got a sweater for Christmas. I really wanted a screamer or a moaner.

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Drag Text/Number and add 1

    Quote 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

    Thanks again in advance, and please forgive me my sins
    [/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.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    Board Regular atmospheric's Avatar
    Join Date
    Jul 2002
    Location
    Leeds, UK
    Posts
    551

    Default Re: Drag Text/Number and add 1

    Sorry Yogi, I meant A5 and B5 of course
    I got a sweater for Christmas. I really wanted a screamer or a moaner.

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default 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
    =

    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?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,416

    Default Re: Drag Text/Number and add 1

    Quote 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

    Thanks again in advance, and please forgive me my sins
    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
    =

    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),"")

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com