Question re: Incrementing using the Fill Handle

Hansoh

New Member
Joined
Feb 20, 2002
Messages
10
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
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 *".
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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