Auto fill sequence

**trakai** · 09-11-2007, 03:26 AM

Hey folks!

I am fighting with this for a longer time now and can't find a solution:
is it possible in Excel to generate a number sequence (to create a list for auto fill) representing numbers like this:

000001
000006
000002
000007
000003
000008
000004
000009
000005
000010
000011
000016
000012
000017
000013
000018
000014
000019
000015
000020

and so on till 001000

Thank you in advance.

**oldchippy** · 09-11-2007, 03:58 AM

Select your cell A1, Format > Custom and enter in the box 000000 (six zero's)

Then enter 1 in A1 the result will be 000001, now you can use auto-fill

**trakai** · 09-11-2007, 04:09 AM

Thank you. This is something I did not know either, though the question
is still open - is it possible to create such a list for auto fill, where the sequence of numbers goes like in the example above?

**oldchippy** · 09-11-2007, 04:40 AM

OK, try something like this, format columns A, B and C as before, then in A1 type 1, A3 type 2, select A1:A4 and auto-fill down.

Select B2, type 6, select B4 type 7, select B2:B5, auto-fill down.

In C1 type this formula and auto-fill down

=IF(A1="",B1,A1)

**trakai** · 09-11-2007, 05:07 AM

Grand. fill down for column A and B works great.
Problem occured with the formula:
=IF(A1="",B1,A1)
Excel screams that the formula contains an error.

so I played with it a bit and changed it's commas with semicolons
=IF(A1="";B1;A1)

It does not complain about any errors anymore though the results after fill down do contain some data I don't need in there.

I attached the resulting excel file to this post. I filled the blocks of correct data with green and the wrong ones with red.

What I am doing wrong?

**oldchippy** · 09-11-2007, 05:08 AM

File not attached? - Got it now

**oldchippy** · 09-11-2007, 05:17 AM

That's OK so far, now to filter out the red one's.

Select D1:D10, type 1 and press Ctrl+Enter

Select D11:D20, type 2 and press Ctrl+Enter

Select D1:D20 and auto-fill down

Select all the data columns and go to Data > Sort, sort by column D with no header, then select all the number 2 rows and delete them.

**trakai** · 09-11-2007, 05:29 AM

Ok I got the idea now. And again I might say - stupid me - the idea is so simple and logic, question is - why I couldn't come up with it?

Ok, final problem here is that the last operation - auto-filling down of D1:D20
seem to work not the way you and I expected.

Please see the attachment.

**oldchippy** · 09-11-2007, 05:42 AM

I forgot to say select D1:D20, hold Ctrl key and fill down - sorry

**trakai** · 09-11-2007, 06:00 AM

Thank you. This might sound as a typical appreciation but I am really thankful for your help and time taken. I use excel in a basic level and never thought about it's unique possibilities. It's not that I need them day by day and it's not that I will start learning it from now on since I need something specific from Excel maybe once ot twice in a year or two or maybe more. One thing is for sure - now I know that it (excel) can give you more than you can imagine and that there are great folks araund who are happy to help you when you're stuck.

Thank you.

**oldchippy** · 09-11-2007, 06:06 AM

If you can come up with a question, it probably can be done in Excel - at least it's worth a try.

It's a pleasure to help - thanks for the feedback

**soccerdon1981** · 09-11-2007, 06:08 PM

I have somewhat of a similar problem (I think).

This is what I have so far in cells A1, A2, A3 ...

=Database!E24
=Database!E40
=Database!E56

If you notice, each cell is increased by 16 cells. When I try to auto fill, the 4th cell always shows up 13 cells less than what I want it to. Is it because I'm pulling data from another tab?

So for cells A4, A5 & A6 I would want it to show ...

=Database!E72
=Database!E88
=Database!E104

But it actually shows ...

=Database!E27
=Database!E43
=Database!E59

What am I doing wrong?

**oldchippy** · 09-12-2007, 05:11 AM

Hi soccerdon1981,

Please read the rules about adding to other posts with a different question.

http://www.excelforum.com/showthread.php?t=613916

In your case if you set up a helper column, you can do it this way. In column A starting in A1=24, A2=40, A3=56 auto-fill down column A.

Then in B1, type this

=INDIRECT("Database!E"&A1) and auto-fill down