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

[hyedipin]

Hi, great site.


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

ary: 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/.

 

[smaigs]

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

 

[Joe4]

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?

 

[lenze]

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

<font face=Courier New><SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Sub</SPAN> Worksheet_Change(<SPAN style="color:darkblue">ByVal</SPAN> Target <SPAN style="color:darkblue">As</SPAN> Range)
    <SPAN style="color:darkblue">If</SPAN> Target.Column <> 1 <SPAN style="color:darkblue">Then</SPAN> <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
    Target.Value = Target.Value + Range("$A$1").Value
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

 

[NateO]

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:

<font face=Courier New><SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Sub</SPAN> Worksheet_Change(<SPAN style="color:darkblue">ByVal</SPAN> Target <SPAN style="color:darkblue">As</SPAN> Range)
    <SPAN style="color:darkblue">If</SPAN> Target.Column <> 2 <SPAN style="color:darkblue">Then</SPAN> <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
    Application.EnableEvents = 0
    Target = Target + Target(, 0)
    Application.EnableEvents = -1
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

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

 

[hyedipin]

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