Results 1 to 6 of 6

Constant Cell/ how to keep it from changing with drag - Help

This is a discussion on Constant Cell/ how to keep it from changing with drag - Help within the Excel Questions forums, part of the Question Forums category; Hi, great site. I just wanted to find a quick answer to my problem. :pary: 1. I remember from school ...

  1. #1
    New Member
    Join Date
    May 2003
    Posts
    2

    Default Constant Cell/ how to keep it from changing with drag - Help

    Hi, great site.


    I just wanted to find a quick answer to my problem.

    :pary: 1.
    I remember from school there was a charecter before cell number to keep it from changing when dragging, while the second one changes in a calculation.

    Like: =(B4+B19) but I want B4 to stay the same, and B19 to change as I drag it down...

    2.

    When calculating , If I want to add the current cell to my calculation, how can I do that?

    Like: Cells have values, and I have one cell at the top. A1 is 1000 and A2 is 100, A3 is 330, A4 is 50.
    On A2 I want something like A1+(currentA2)=(NewA2) in to A2, and drag this down with A1 as constant, and so on with other cells.

    I hope I made myself clear with the questions, and If you don't understand my questions, it's ok .. but I am sure it is very easy to solve.. Please help ASAP/.

  2. #2
    New Member
    Join Date
    Apr 2003
    Location
    An Englishman in Ireland
    Posts
    28

    Default Re: Constant Cell/ how to keep it from changing with drag -

    In answer to question 1 select the cell you want to stay constant ie B4 and press F4 as this makes that cell the absolute reference

    I am still investigating question 2

  3. #3
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,235

    Default Re: Constant Cell/ how to keep it from changing with drag -

    1. =$B$4+B19

    2. It cannot be done without Visual Basic (a macro or custom function). Otherwise, you will get a circular reference error. Are you interested in using Visual Basic to solve this?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Constant Cell/ how to keep it from changing with drag -

    1. =$B$4+B19 0r give B4 a Name such as 'myvalue' and use =myValue+B19

    2. You will have to use code to do this as I see Jimiskey has suggested

    Maybe something like

    Private Sub Worksheet_Change(ByVal Target As Range)
    ****If Target.Column <> 1 Then Exit Sub
    ****Target.Value = Target.Value + Range("$A$1").Value
    End Sub

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: Constant Cell/ how to keep it from changing with drag -

    #2 should really not be a requirement. You have 256 columns and cut & paste special at your disposal, in addition to vba. Use a third column as a spreadsheet was intended to function. This drastically simplifies your quandary.

    Edit: If you really want to do this, tweak Lenze's procedure to look like:

    Private Sub Worksheet_Change(ByVal Target As Range)
    ****If Target.Column <> 2 Then Exit Sub
    ****Application.EnableEvents = 0
    ****Target = Target + Target(, 0)
    ****Application.EnableEvents = -1
    End Sub


    For a variety of reasons. Put it in the appropriate worksheet module.

  6. #6
    New Member
    Join Date
    May 2003
    Posts
    2

    Default Re: Constant Cell/ how to keep it from changing with drag -

    Thank you all for your comments and greatly fast replies. Saved my life, and made me ashamed of myself as I think how I could forget that..

    I decided to just add second column and work on that instead of fiddling with VB, even though I loved VB6 but never studied enough to master it.. Maybe in the future.

    Anyway.

    Thanks to all again..

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