Results 1 to 4 of 4

Continuing the pattern of a formula to adjacent cells

This is a discussion on Continuing the pattern of a formula to adjacent cells within the Excel Questions forums, part of the Question Forums category; Ok so let's say I have numbers in cells A1 to A10. Now I want all those numbers to be ...

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    3

    Default Continuing the pattern of a formula to adjacent cells

    Ok so let's say I have numbers in cells A1 to A10. Now I want all those numbers to be copied to cells C5 to C15. I used Excell 2 years ago (could have been excel 2000 or 2003). Now 2 years ago, all I had to do was take cell C5 and put in the formula box "=A1". Then for cell C6, I would put "=A2" and for cell C7, "=A3". Then, i could select cells C5 to C7 and fill (I think that's the word. I mean pressing on the little black square on the bottom right corner and extending it to other cells) the cells up to C15. Excel would figure out the pattern of the formula in the first 3 cells and extend it to the next cells. Now, I have excel 2007 and if I try this, it will not work. Now, when I use the little black square, it just copies the formula to the other cells, it doesn't continue the pattern of the formula.
    Is this an option I can turn on somewere? I can't do anything without this function.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,305

    Default Re: Continuing the pattern of a formula to adjacent cells

    Quote Originally Posted by SilenTScopE View Post
    Ok so let's say I have numbers in cells A1 to A10. Now I want all those numbers to be copied to cells C5 to C15. I used Excell 2 years ago (could have been excel 2000 or 2003). Now 2 years ago, all I had to do was take cell C5 and put in the formula box "=A1". Then for cell C6, I would put "=A2" and for cell C7, "=A3". Then, i could select cells C5 to C7 and fill (I think that's the word. I mean pressing on the little black square on the bottom right corner and extending it to other cells) the cells up to C15. Excel would figure out the pattern of the formula in the first 3 cells and extend it to the next cells. Now, I have excel 2007 and if I try this, it will not work. Now, when I use the little black square, it just copies the formula to the other cells, it doesn't continue the pattern of the formula.
    Is this an option I can turn on somewere? I can't do anything without this function.
    Welcome to the MrExcel board!

    I am not exactly sure what you are saying goes wrong, but this works exactly the same for me in Excel 2007 as in previous versions. Try just putting =A1 into cell C5 then click and drag the "Fill Handle" (the little black square you described).

    If this doesn't work, please tell us what is in cells A1:A10, what formulas end up in C5:C14 after you perform the above process, and what results appear in C5:C14 after the process?
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    3

    Default Re: Continuing the pattern of a formula to adjacent cells

    Sorry, instead of C5 to C15, I meant from C5 to G15. So yes, it works if I do it from C5 to C15.
    So let's say Cell A1 has a value of 2. A2=3, A3=4, A4=5, A5=6
    Now in cell C5, I put the formula "=A1" and I get a value of 2. That's great.
    Now I drag the "fill handle" of cell C5 up to cell G5, and what I would want is that cell D5 gets the value of cell A2, E5=A3, F5=A4 and G5=A5. This does not happen. Instead, E5 gets the formula "=B1" etc.
    So it seems to only work when you are doing it parallel. If I want to do this perpendicularly (from a column to a row) it does not work. That's what I meant. Is there a way around this?

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,305

    Default Re: Continuing the pattern of a formula to adjacent cells

    Quote Originally Posted by SilenTScopE View Post
    Sorry, instead of C5 to C15, I meant from C5 to G15. So yes, it works if I do it from C5 to C15.
    So let's say Cell A1 has a value of 2. A2=3, A3=4, A4=5, A5=6
    Now in cell C5, I put the formula "=A1" and I get a value of 2. That's great.
    Now I drag the "fill handle" of cell C5 up to cell G5, and what I would want is that cell D5 gets the value of cell A2, E5=A3, F5=A4 and G5=A5. This does not happen. Instead, E5 gets the formula "=B1" etc.
    So it seems to only work when you are doing it parallel. If I want to do this perpendicularly (from a column to a row) it does not work. That's what I meant. Is there a way around this?
    Yes, the Fill Handle only works 'parallel' when using such a simple formula like your example. By the way, it has always worked like that so it is nothing to do with Excel 2007.

    However, by using an alternative formula, you can achieve what you want. Put the formula below in C5 and drag the Fill Handle across.

    Transpose

     ABCDEFG
    12      
    23      
    34      
    45      
    56 23456

    Spreadsheet Formulas
    CellFormula
    C5=INDEX($A$1:$A$10,COLUMNS($C5:C5))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com