Filling a series

See reply in .Excel

Biff

"jhct" <[email protected]> wrote in message
news:[email protected]...
>
> Hi, here's a plain example of what I'm trying to do:
> In cell B1 is the formula: =A1
> In cell B2 is the formula: =A1
> In cell B3 is the formula: =A2
> In cell B4 is the formula: =A2
> In cell B5 is the forumla: =A3
> In cell B6 is the formula: =A3
>
> (you can see where this is heading..)
>
> The problem I have is that I want to use the fill handle to copy this
> series down a whole column, however Excel just won't see the pattern
> here! (or I'm missing something obvious)
>
> Help please!
>
>
> --
> jhct
> ------------------------------------------------------------------------
> jhct's Profile:
> http://www.excelforum.com/member.php...o&userid=31562
> View this thread: http://www.excelforum.com/showthread...hreadid=512566
>

sorry i dont' follow...I've searched this topic and couldnt' find anything, can you please clarify. thanks

Select cells B1 and B2
grab the autofill handle and drag down as required.

Hans

cheers for the response Hans, I tried this first but it doesn't work. Excel populates the following formulas down column B when dragging the fill handle:
=A1
=A1
=A2
=A2
=A5 << it skips 3 here
=A5
=A6
=A6
=A9 << skips another 3
=A9
=A10
=A10 etc.....(continues to do this)

Any other sugesstions?

It does work, look at this example

1 1
2 1
3 3
4 3
5 5
6 5
7 7
8 7
9 9
11 9
22 22
33 22
44 44
55 44
66 66
77 66
88 88
99 88

Could you try again?

Hans

Are there maybe any hidden rows?

Hans

There are no hidden rows, I will clarify what I mean with an image.

What I want to do is use the fill handle to repeat this series down colum B. So I want cells B5 and B6 to populate as =A3, then the next 2 cells down =A4.


Your example above goes up in odd numbers missing all the even numbers in between, which is my problem. I need each 2 cells to be the next consecutive number.

Say you want your sequence for 100 cells.

You could try putting =a1 in both cells B1 and C1, etc, through row 50, then drag the range C1:C50 down to B51. Fill col C the consecutive sequence 1,2,3,etc for the range c1:c50, and again for C51:C100. Then sort B1:C100 on col C. this shoudl give you what you're after.

---Glenn

Hi!

>See reply in .Excel

>>sorry i dont' follow...I've searched this topic and couldnt'
>>find anything, can you please clarify. thanks

You have workable solutions at your other post in the Public.Excel group.

While you're getting nowhere in this thread the solution has already been
provided. This is why multi-posting is a bad idea!

Enter this formula in B1 and copy down as needed:

=INDEX(A:A,CEILING(ROWS($1:1)/2,1))

Biff

"jhct" <[email protected]> wrote in message
news:[email protected]...
>
> There are no hidden rows, I will clarify what I mean with an image.
>
> What I want to do is use the fill handle to repeat this series down
> colum B. So I want cells B5 and B6 to populate as =A3, then the next 2
> cells down =A4.
>
>
> Your example above goes up in odd numbers missing all the even numbers
> in between, which is my problem. I need each 2 cells to be the next
> consecutive number.
>
>
> +-------------------------------------------------------------------+
> |Filename: example.JPG |
> |Download: http://www.excelforum.com/attachment.php?postid=4364 |
> +-------------------------------------------------------------------+
>
> --
> jhct
> ------------------------------------------------------------------------
> jhct's Profile:
> http://www.excelforum.com/member.php...o&userid=31562
> View this thread: http://www.excelforum.com/showthread...hreadid=512566
>

Sorry about the multiple post! I thought I had moved it rather than duplicated it.

But anyway - that formula worked! Thank you so much - you're a genius and you won't' beleive how much time you've saved me!! thanks so much.