Results 1 to 7 of 7

Copying a formula in a column while keeping one cell a fixed value

This is a discussion on Copying a formula in a column while keeping one cell a fixed value within the Excel Questions forums, part of the Question Forums category; I'm trying to duplicate a formula from one cell to all the remaining cells below. I want one cell to ...

  1. #1
    New Member
    Join Date
    Dec 2011
    Posts
    4

    Default Copying a formula in a column while keeping one cell a fixed value

    I'm trying to duplicate a formula from one cell to all the remaining cells below. I want one cell to remain a constant fixed value in the next cell with the formula using the adjoining cell. The adjoining cell needs to increase in the next cell with one value remaining constant. I know there is an easy way to do this and has probably been covered.
    Thank you

  2. #2
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Copying a formula in a column while keeping one cell a fixed value

    Quote Originally Posted by mtnfisherman63 View Post
    I'm trying to duplicate a formula from one cell to all the remaining cells below. I want one cell to remain a constant fixed value in the next cell with the formula using the adjoining cell. The adjoining cell needs to increase in the next cell with one value remaining constant. I know there is an easy way to do this and has probably been covered.
    Thank you
    Post the formula.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  3. #3
    New Member
    Join Date
    Dec 2011
    Posts
    4

    Default Re: Copying a formula in a column while keeping one cell a fixed value

    =AG5/I2
    the value at I2 needs to remain constant while the value of AG5 needs to increase by 1. This needs to repeat the entire column.

  4. #4
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Copying a formula in a column while keeping one cell a fixed value

    Quote Originally Posted by mtnfisherman63 View Post
    =AG5/I2
    the value at I2 needs to remain constant while the value of AG5 needs to increase by 1. This needs to repeat the entire column.
    Try this...

    =AG5/I$2

    The dollar sign will keep the row number constant as the formula is copied down a column.

    You can do the same thing with the column letter:

    =AG5/$I2

    The dollar sign will keep the column letter constant as the formula is copied across a row.

    You can use any combination as is needed depending on how you want to copy the formula:

    =AG5/I2
    =$AG$5/$I$2
    =$AG5/$I2
    =AG$5/I$2
    =AG$5/$I2
    =$AG5/I$2

    This is known as absolute and relative cell referencing. The dollar sign makes that row/column absolute. When the dollar sign is absent then that row/column reference is relative.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  5. #5
    New Member
    Join Date
    Dec 2011
    Posts
    4

    Default Re: Copying a formula in a column while keeping one cell a fixed value

    Thank you for the information. Worked well

  6. #6
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Copying a formula in a column while keeping one cell a fixed value

    Quote Originally Posted by mtnfisherman63 View Post
    Thank you for the information. Worked well
    You're welcome. Thanks for the feedback!
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  7. #7
    New Member
    Join Date
    Nov 2013
    Posts
    1

    Default Re: Copying a formula in a column while keeping one cell a fixed value

    Thank-you so much T. Valko for stopping the anguish of not being able to figure this out and not finding anything on the internet until reading your post. I have never signed up for a forum site but had to just to thank-you for the help

Tags for this Thread

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