# 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. ## 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. ## 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. ## 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?

4. ## 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. ## 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. ## 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..

#### Posting Permissions

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