Solved

Excel 2002 - Autofill options greyed out

Posted on 2002-06-29
12
680 Views
Last Modified: 2012-06-22
I'm attempting to use Autofill in Excel 2002 to extend a range of consecutive dates.  I have used Autofill many times before in other versions of Excel but have recently upgraded to Office XP.

The problem is that when I select say, the last five dates in the range I want to extend, then drag the fill handle down the column, all Excel wants to do is repeat the list, not extend it.  If I right-click the fill handle and drag down the column, the menu that pops up only has "Copy Cells" as an option, all of the Autofill selections are greyed out.

Thanks in advance for any help you can offer.
0
Comment
Question by:thescud
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 7118316
Hi Jeremy M,

How are your dates formatted as text? because then it seems logical for excel to fill as repeat
If you got real dates then it should be no problem

HAGD:O)Bruintje
0
 
LVL 7

Expert Comment

by:ildc
ID: 7118414
Hi Jeremy,

the autofill only works correctly if you use only the last two dates. If you use more, than excel will make a repetitive copy of all selected dates.

Regards
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7118470
Hi ildc that's not completely true since i can do it wit 4 or more dates selected, i can drag them filling down or doing it through the menu

Brian
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 44

Accepted Solution

by:
bruintje earned 75 total points
ID: 7118493
Jeremy, have you tried to do it through the menu? it seems that excel doesn't know what to fill in and that's why it is greyed out, you can do an autofill also through

-select the range you want to fill
-the first or more cells contain the value
-then choose | edit | fill | series | make a choice | OK

just let us know
Brian
0
 

Author Comment

by:thescud
ID: 7118644
bruintje:
The dates are formatted as dd/mm/yy.  That was one of the first things that I checked.
I attempted your second suggestion also.  I selected a column of about 100 cells, the top two being 03/07/02 and 04/07/02.  When I asked Excel to fill in the column from the Edit menu, it filled every cell with 03/07/02.

ildc:
I agree with bruintje.  It doesn't matter the number of cells you select to determine your pattern.

0
 
LVL 7

Expert Comment

by:ildc
ID: 7118668
Bruintje,

You're right, I tested it with non-consecutive dates and then you get copies.
0
 
LVL 7

Expert Comment

by:ildc
ID: 7118687
Jeremy,

Try once ctrl+;  .This gives you the exact date of today no matter the formatting of your system. If you copy it down and you still get the same problem, than I'm out of answers.

Regards
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7118689
one other thing to check is to see that you have Calculation as automatic

Tools --> Options --> Calculation --> Calculation: automatic

if it is non automatic it could hold up your fill down
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 7119196
A sure way to make sure your cells are truly formatted as dates:

Put a one in any blank cell. Copy that cell.
Select one of your date cells. Hit Edit-Paste special, Multiply.
Today's date would return 37436. If it returns anything else, it's not formatted as a date.

Let us know if you need help converting text to a date.

~Dreamboat
www.TheWordExpert.com
0
 

Author Comment

by:thescud
ID: 7119453
I was finally able to get Autofill to work properly, though the fix wasn't one of those offered.  The particular worksheet that I was using had a frozen pane, across row 12.  The range of dates that I was trying to extend ended around row 330.  This time I selected the entire range of dates and dragged the fill handle and it filled in the series properly.

Since then I've experimented with Autofill and it works as it should everytime, whether I select the entire range of dates or just a few.

This is an odd one.  I've been using Excel for 8 years so it wasn't a newbie error.  

Thanks everyone for your suggestions.  I hate to leave this question open so I'll award the points to bruintje.  Early bird gets the worm.
0
 

Author Comment

by:thescud
ID: 7119458
This was frustrating.  Maybe a bug?
Microsoft can't pass this one off as a "feature".
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7119629
thanks but next time just ask for a refund and let Customer Support close this question as a PAQ, you solved this yourself and that's a solution too

i always comment to let the asker choose, but please don't give the C grade because it's ruining my record

rather get no points then a C
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question