Excel Circular Reference SUM HELP!

[SpecOpBookie]

Hi all, I'm fairly new with Excel so let's see if I can get a quick answer
for my question .

I am trying to add a base number to whatever number i Input within the same
cell. For example: The Cell I am working with is I4. The base number I want
is 3500. I want that added to whatever I type in the cell and summed in the
same cell. So if I type in 2200, the sum would be 5700. =SUM(3500,I4) does
not work. I get a 0 in the cell and if I try to input 2200, the formula just
erases. I need this formula for alot of cells all with the base number of
3500 and random input numbers. This IS possible isn't it?

 

[TR Young]

I don't believe that is possible. What you could do is insert a column to
the right of the colum in which you are entering the random input number.
Then, starting in the first cell of the column you just added, use the
formula =SUM(I4+3500). Then, do a copy and past all the way down the new
column. that way, whenever you enter any numbers in the "I" column, the new
"J" column will do the sum for you.
You can't have a circular reference in a cell and have it work, though. not
possible.

 

[Daniel.C]

Hi.
Paste the following macro in the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target
If IsNumeric(c) Then
Application.EnableEvents = False
c = 3500 + c
Application.EnableEvents = True
End If
Next c
End Sub
(Right click on the sheet tab, select "View code" and paste the macro)
HTH
Daniel

 

[SpecOpBookie]

Thanks for the quick replies everyone. Daniel, do I need to edit this macro
to ONLY run for the "I" Column? I do not want to add 3500 to all numbers i
input. just to all of the numbers i input in the "I" Column THANKS!

 

[SpecOpBookie]

EDIT: I got it Daniel! I canged all of the "c"'s to "I" and it wors and runs
great. THANK YOU! I never thought of using a basic macro like that. you guys
rule. KUDOS to you.

 

[Luke M]

Small note, the logical results TRUE and FALSE are characterized as numeric,
so if you input either of those into your spreadsheet, FALSE will become
3500, and TRUE will become 3499.

 

[Luke M]

Also a WARNING:
Deleting cells or cell contents creates a value of 0, thus activating macro.
This can lead to great annoyance if deleting entire columns/ranges of data.

 

[SpecOpBookie]

Uhoh, not done yet. I checked, and the macro given to me changes ALL cells
that I input any numeric value into... I dont want that. can I specify a
range? just for the column "I"?

 

[Luke M]

Macro limited to changes in column I

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address Like "$I$*" Then
Dim c As Range
For Each c In Target
If IsNumeric(c) Then
Application.EnableEvents = False
c = 3500 + c
Application.EnableEvents = True
End If
Next c
End If
End Sub

 

[SpecOpBookie]

almost there haha... So now I cant get it to run. I changed the macro to
ACTIVATE and that didnt help, saved, exited, and re-opened it... notta. Excel
really isn't my strong suit.

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Const WS_RANGE As String = "I:I"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each c In Intersect(Target, Me.Range(WS_RANGE)).Cells
If Application.IsNumber(c) Then
Application.EnableEvents = False
c = 3500 + c
Application.EnableEvents = True
End If
Next c
End If
End Sub


Gord Dibben MS Excel MVP

 

[Luke M]

Events may have become disabled. Unlike most other functions, the
EventsEnabled does not reset if a macro is halted.

Press Ctrl+G to pring up Immediate Action windown, and input
Application.EnableEvents = True
and press enter.

Now check to see if the macro works. Also, change the macro back to "Change"
not activate.