How to copy formula with one cell constant

Discussion in 'Microsoft Excel Worksheet Functions' started by Mark, Jan 29, 2008.

  1. Mark

    Mark Guest

    How can I copy the formula so that B2 stays constant and only the row changes?

    A B C

    1 2

    2 10 =A2*B1

    3 20 =A3*B1

    4 30 =A4*B1


    Thanks
     
    Mark, Jan 29, 2008
    #1
    1. Advertisements

  2. Mark

    Pete_UK Guest

    Put this formula in B2:

    =A2*B$1

    When you copy this down the $1 will not change.

    Hope this helps.

    Pete

    On Jan 29, 7:10 pm, Mark <> wrote:
    > How can I copy the formula so that B2 stays constant and only the row changes?
    >
    >       A      B     C
    >
    > 1           2
    >
    > 2   10    =A2*B1
    >
    > 3   20    =A3*B1
    >
    > 4  30     =A4*B1
    >
    > Thanks
     
    Pete_UK, Jan 29, 2008
    #2
    1. Advertisements

  3. Mark

    Stan Brown Guest

    Tue, 29 Jan 2008 11:10:05 -0800 from Mark
    <>:
    > How can I copy the formula so that B2 stays constant and only the row changes?
    >
    > A B C
    >
    > 1 2
    >
    > 2 10 =A2*B1
    >
    > 3 20 =A3*B1
    >
    > 4 30 =A4*B1


    ?? B2 ??

    --
    Stan Brown, Oak Road Systems, Tompkins County, New York, USA
    http://OakRoadSystems.com/
    "If there's one thing I know, it's men. I ought to: it's
    been my life work." -- Marie Dressler, in /Dinner at Eight/
     
    Stan Brown, Jan 31, 2008
    #3
  4. Mark

    Guest

    Dear pete_uk,

    Thanks a ton,

    saved lot my time and appricate your effort on this.
    Regards,
    Dr. Shashi
    www.holidaymantra.com
    On Wednesday, January 30, 2008 12:52:15 AM UTC+5:30, Pete_UK wrote:
    > Put this formula in B2:
    >
    > =A2*B$1
    >
    > When you copy this down the $1 will not change.
    >
    > Hope this helps.
    >
    > Pete
    >
    > On Jan 29, 7:10 pm, Mark <> wrote:
    > > How can I copy the formula so that B2 stays constant and only the row changes?
    > >
    > >       A      B     C
    > >
    > > 1           2
    > >
    > > 2   10    =A2*B1
    > >
    > > 3   20    =A3*B1
    > >
    > > 4  30     =A4*B1
    > >
    > > Thanks
     
    , Jul 7, 2012
    #4
  5. Mark

    Mark Flanagan

    Joined:
    Jul 14, 2012
    Messages:
    2
    Likes Received:
    0
    A $ sign before the column or row keeps it constant when pasted / dragged.
    You can scroll through the options by pressing F4 when the cell reference is highlighted.
    i.e.

    $A$2
    A$2
    $A2
    A2

    and then the cycle repeats

     
    Mark Flanagan, Jul 14, 2012
    #5
  6. Mark

    Guest

    On Tuesday, January 29, 2008 2:10:05 PM UTC-5, Mark wrote:
    > How can I copy the formula so that B2 stays constant and only the row changes?
    >
    > A B C
    >
    > 1 2
    >
    > 2 10 =A2*B1
    >
    > 3 20 =A3*B1
    >
    > 4 30 =A4*B1
    >
    >
    > Thanks


    GOOD JOB GUYS
     
    , Aug 10, 2012
    #6
  7. Mark

    Guest

    On Tuesday, 29 January 2008 20:10:05 UTC+1, Mark wrote:
    > How can I copy the formula so that B2 stays constant and only the row changes?
    >
    > A B C
    >
    > 1 2
    >
    > 2 10 =A2*B1
    >
    > 3 20 =A3*B1
    >
    > 4 30 =A4*B1
    >
    >
    > Thanks


    Very helpful - quickly solved a problem that I had never gotten round to resolving. Thanks, so much!
     
    , Mar 20, 2014
    #7
  8. Mark

    wabbleknee Guest

    Try $B$2

    wrote in message
    news:...

    On Tuesday, 29 January 2008 20:10:05 UTC+1, Mark wrote:
    > How can I copy the formula so that B2 stays constant and only the row
    > changes?
    >
    > A B C
    >
    > 1 2
    >
    > 2 10 =A2*B1
    >
    > 3 20 =A3*B1
    >
    > 4 30 =A4*B1
    >
    >
    > Thanks


    Very helpful - quickly solved a problem that I had never gotten round to
    resolving. Thanks, so much!
     
    wabbleknee, Mar 22, 2014
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    how do you copy a formula and keep the lookup array constant

    Guest, Oct 19, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    3,024
    Guest
    Oct 19, 2004
  2. Guest

    Copy a formula maintaining a constant cell

    Guest, Jul 26, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    189
    JE McGimpsey
    Jul 26, 2005
  3. Guest

    How do I copy a function with a called cell constant?

    Guest, Sep 6, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    179
    JenLook
    Sep 8, 2005
  4. Guest

    Excel: Format Control: 'cell link' reamins constant. How to copy

    Guest, Sep 7, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    694
    Guest
    Sep 7, 2005
  5. Guest

    Constant loan payments vs. constant payments of principal

    Guest, Dec 20, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    663
Loading...

Share This Page