Question re: Incrementing using the Fill Handle

[Hansoh]

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

[IML]

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.

[Mark W.]

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

[Hansoh]

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

[Bob Umlas]

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 ]

[Mark W.]

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 ]