Question re: Incrementing using the Fill Handle
Question re: Incrementing using the Fill Handle
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Question re: Incrementing using the Fill Handle

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Massachusetts
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    if i have a date or a number in a cell, i can drag the Fill Handle (black plus sign that appears when i hover over the bottom right hand corner of a cell) to increment.

    for example, if i have January or the number 1, i can drag and easily get February, March, etc.

    but if i have 'January RRR' or 'January *' or anything but January, the fill handle will not recognize the month and increment to 'February RRR' or 'February *'. what can i do? does anyone have a creative solution to said problem? thanks in advance!


    han

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A few options:

    1) by formula you could use
    =TEXT(EDATE("01/01/01",ROW()-1),"MMMM")&" your data"

    for months. this requres the analyst tool pak to be install.

    2) Use fill as you normally would to get your number / date. Use the concencate function to include your special text. You could then copy paste special values and delete the orignal if you like.

    3) Create custom lists under Tools - Option - Custom lists.
    Then when you type in a value on the list, right click on the fill handle as you move down. When you release, select the Fill Series button.


  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here are 2 approaches...

    1. After typing "January" into cell A1 and filling to cell A12 (ending with "December") enter the formula, =A1&" *", in cell B2 and copy down to cell B12.

    2. Create a custom list consisting of "January *", "February *", etc. using the Tools | Options... menu command and choosing the Custom Lists tab. Once this list has been configured you can automatically fill the series beginning with "January *".

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Massachusetts
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks mark.
    thanks IML.

    custom lists is the way to go; didn't even know about that feature. i am trying to automate some spreadsheets using dropdown listboxes. i'm allowing users to drag down choices across multiple drop down listboxes to simplify. i noticed that dragging down a listbox with 'january' incremented, but 'january YTD' or 'january variance' did not and that inability kind of handcuffed me. your advice to use 'custom lists' is the WAY to go. however, i think i have to go to everyone else's desktops here at the office and import custom lists for my people so that they can have the same functionality as i do; my guess is that OPTIONS is specific to each MSExcel application and NOT specific to each file, right? thanks again!!!!


    han

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    NY
    Posts
    1,041
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Even easier is enter January in a cell, then format that cell as
    @" RRR"
    You'll see January RRR
    Filldown will show February RRR, etc.

    [ This Message was edited by: Bob Umlas on 2002-03-20 13:54 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-20 13:50, Hansoh wrote:
    thanks mark.
    thanks IML.

    custom lists is the way to go; didn't even know about that feature. i am trying to automate some spreadsheets using dropdown listboxes. i'm allowing users to drag down choices across multiple drop down listboxes to simplify. i noticed that dragging down a listbox with 'january' incremented, but 'january YTD' or 'january variance' did not and that inability kind of handcuffed me. your advice to use 'custom lists' is the WAY to go. however, i think i have to go to everyone else's desktops here at the office and import custom lists for my people so that they can have the same functionality as i do; my guess is that OPTIONS is specific to each MSExcel application and NOT specific to each file, right? thanks again!!!!


    han
    Yes, Custom Lists are not "carried" with the worksheet. One solution would be to attach a VBA with your worksheet that executes when opened, checks for the presence of your custom list and configures the list as needed.

    This would insulate you from tampering and eliminate the need for you to "visit" your users -- unless, of course, they're friendly!!!

    Bob's suggestion above is a good one too. If you intend to use "January *" as a comparison or lookup value you'll have to concatenate " *" to the contents of these cells.

    [ This Message was edited by: Mark W. on 2002-03-20 14:13 ]

User Tag List

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