+ Reply to Thread
Results 1 to 12 of 12

How do I copy formulas and automatically change reference cells

  1. #1
    Registered User
    Join Date
    10-16-2008
    Location
    Perth Aus
    Posts
    16

    How do I copy formulas and automatically change reference cells

    This may be basic, but so am I...

    I have a 2 columns (A and B) next to each other that is using a Sum formula and other multiplying formulas. They reference 3 cells in a different column (C) to come up up with the answer for A and B. Therefore A1 and B1 reference C1 C2 and C3 (and possibly if want to use in future column D with D1 D2 D3).

    Then in A2 and B2 I want to reference from C4, C5, C6
    Then in A3 and A4 I want to reference from C7, C78, C9 etc.... and so on...

    Instead of retyping formulas for each row in A and B, I want to copy down, but I getting the wrong answer when I do that.

    I am therefore assuming I typing the formula wrong. How do I type it correctly so the cell reference changes automatically when I pull down columns A and B?

    Formula for Column A: =SUM($G28:$G30)
    Forumla for Column B: =($G28*$H28+$G29*$H29+$G30*$H30)/$B17

    Therefore next row should be referenced from G31 to G33 and H31 to H33

    But when I copy it only adjusts it for 1 reference down.

    Thanks!
    Last edited by Macca1888; 10-18-2008 at 07:30 PM. Reason: added formulas

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Please change your thread title to something descriptive, and then post your formula or a sample workbook.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-16-2008
    Location
    Perth Aus
    Posts
    16
    Done Sorry about that

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Post your formula or a sample workbook?

  5. #5
    Registered User
    Join Date
    10-16-2008
    Location
    Perth Aus
    Posts
    16
    Formula for Column A: =SUM($G28:$G30)
    Forumla for Column B: =($G28*$H28+$G29*$H29+$G30*$H30)/$B17

    Therefore next row should be referenced from G31 to G33 and H31 to H33

    The workbook is a mess at the moment

  6. #6
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    G'day Macca,

    Firstly I would try this,

    Highlight the range A1:B3 then copy down.

    HTH

    because I've got no more suggestions.
    Have I made you happy ??? If yes, please make me happy by pressing the http://www.excelforum.com/images/buttons/reputation-40b.png Add Reputation button in my post.
    Please don't forget to do the same to other contributors of this forum.

    Thanks
    I don't void confusion, I create it

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Ratcat makes an excellent suggestion. Then delete the empty rows.

    But first, change the formula in col B to

    =sumproduct($G28:$G30, $H28:$H30) / $B17
    Last edited by shg; 10-18-2008 at 07:04 PM. Reason: correction

  8. #8
    Registered User
    Join Date
    10-16-2008
    Location
    Perth Aus
    Posts
    16
    Quote Originally Posted by shg View Post
    Ratcat makes an excellent suggestion. Then delete the empty rows.

    But first, change the formula in col B to

    =sumproduct($G28:$G13, $H28:$H30) / $B17
    Is that meant to be G30?

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Yup ......

  10. #10
    Registered User
    Join Date
    10-16-2008
    Location
    Perth Aus
    Posts
    16
    Doesnt seem to work.

    I highlighted 3 rows with the corrected formula, pulled down, and got the wrong answers. The incorrect formulas in the new cells after pulling down from above were:

    B : =SUM($G25:$G27)
    C: =SUMPRODUCT($G25:$G27, $H25:$H27) / $B18

    They were supposed to have reference G31: G33 and H31:H33

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    If it didn't leave two blank rows between each group of formulas, you didn't do it as suggested.

    Enter the formulas in one row.

    Select those cells and the two rows of vacant cells below them

    Now drag down.

    Delete the empty rows.

  12. #12
    Registered User
    Join Date
    10-16-2008
    Location
    Perth Aus
    Posts
    16
    Quote Originally Posted by shg View Post
    If it didn't leave two blank rows between each group of formulas, you didn't do it as suggested.

    Enter the formulas in one row.

    Select those cells and the two rows of vacant cells below them

    Now drag down.

    Delete the empty rows.
    Yep, got ya now.

    Cheers guys. Thanks for the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Backward subtraction forumla!
    By stewart08 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-04-2008, 04:18 PM
  2. modify a forumla to adjust
    By Martindelica in forum Excel General
    Replies: 6
    Last Post: 02-25-2008, 02:02 AM
  3. divide the final value
    By psiron in forum Excel General
    Replies: 4
    Last Post: 12-06-2007, 10:30 AM
  4. Forumla Displays??
    By pba.mike in forum Excel General
    Replies: 9
    Last Post: 03-31-2007, 10:54 AM
  5. Countif forumla question with time, please help!
    By karch35 in forum Excel General
    Replies: 3
    Last Post: 11-15-2006, 02:00 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1