Filling a series

[jhct]

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!

 

[Biff]

See reply in .Excel

Biff

 

[jhct]

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

 

[flummi]

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

Hans

 

[jhct]

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?

 

[flummi]

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

 

[flummi]

Are there maybe any hidden rows?

Hans

 

[jhct]

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 |
+-------------------------------------------------------------------+

 

[Biff]

Hi!

See reply in .Excel

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

 

[gjcase]

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

 

[jhct]

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.