How can I leave the contents of a cell alone in an IF statement?

K

Ken

This is so simple I cannot believe I can't see the answer. I have a
situation where column A is the current weeks totals (ex: a1 = 6, a2 = 10, a3
= 12). I need to have column b equal the month-to-date totals (b1 = b1+a1,
b2 = b2+a1, b3=b3+a3). I realize this is dealing with circular references,
but I find it hard to believe that a situation like this cannot be easily
resolved. Anyone come across this and find a solution?
 
N

Niek Otten

B1: =A1
B2: =B1+A2, Copy down as far as needed

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| This is so simple I cannot believe I can't see the answer. I have a
| situation where column A is the current weeks totals (ex: a1 = 6, a2 = 10, a3
| = 12). I need to have column b equal the month-to-date totals (b1 = b1+a1,
| b2 = b2+a1, b3=b3+a3). I realize this is dealing with circular references,
| but I find it hard to believe that a situation like this cannot be easily
| resolved. Anyone come across this and find a solution?
 
K

Ken

Thanks Niek, but that is not quite what I am looking for. I need each line
to be it's own mtd total. Let me see if I can describe it a little
differently:
Column A is the category description.
Column B is the current weekly total for the category.
Column C is the month-to-date total for the category.

I am looking to have the contents of cell b1 added to c1 to produce a
month-to-date amount for the category described in a1. (I want c1 to = c1 +
b1)
I want c2 to represent the month-to-date amount for the category described
in a2.
(I want c2 to = c2 + b2).

c1 and c2 are mutually exclusive.

I hope this makes more sense.

Thanks,
Ken
 
B

Bernard Liengme

An alternative to Niek's solution is:
=SUM($A$1:A1) in B1 and copy down as needed
and if you want to see nothing when the A cell is empty:
=IF(ISBLANK(A1),"",SUM($A$1:A1))
best wishes
 
K

Ken

Thanks Bernard. What you provided will work if I wanted more of a running
MTD total. What I have is a spread sheet that looks similar to this:
As you can see the WTD amount will be entered for each cat. What I am
looking for is a way to add across to get the MTD value for Cat A. (in other
words MTD = MTD + WTD for Cat A; 3 + 0 = 3 (assuming this is the first week
of the month). I can use the SUM function to get the grand total of all MTD
values.
I hope the example explains my situation. This seems so simple in concept,
but I think I can't see the forest for the trees.

Company A
Date Goes Here
Categories WTD MTD YTD
Group 1 12 12 301 (note: @sum is used to get these ttls)
Cat A 3 3 120 (this ex represents the first week)
Cat B 1 1 16
Cat C 3
Cat D 1 1 3
Cat E 2
Cat F 4 4 58
Cat G 3
Cat H 2 2 76
Cat I 2
Cat J 1 1 9
Cat K 8
Cat L 1
 
B

Bernard Liengme

In A5 you have the text: Cat A
In B5 is the WTD value for Cat A and the value is 3
In C5 you want 3 fro MTD
In D5 the YTD total is 120
Now I am going to enter THIS weeks WTD total (say 5) where does it go?
Is MTD now 8 and YTD 125?
Would love to help but still have not got head around the problem
 
K

Ken Johnson

Hi Ken,

A cell's formula is erased once you type in a value.

What you are trying to do can be done using a VBA worksheet Event
Procedure. The procedure runs everytime a cell value on that sheet
changes.
To do what you are wanting, the code in the procedure first checks if
the cell(s) whose value(s) changed are in column C starting at C2. If
Yes then the code continues, if No then the code ends.
If cells in the range C2:C65536 changed then the code turns off, or
disables, Events to avoid looping caused by self-triggering, then each
cell in column C whose value changed has the value of the corresponding
cell in column B added to it. If the column B cell is not a number then
the C cell value remains at what ever value was typed in.
After all the cells have been processed the code then enables Events so
that the Event procedure will be ready to run the next time any cells
change.

The only problem with event procedures is that they are macros and
macros only work if the Security Level allows them to run and then only
if the user clicks the "Enable Macros" button on the "Security Warning"
dialog that appears when the workbook is opened.

If you decide to try out the code below (on a COPY of your workbook of
course) then first change your Security level to Medium by going
Tools|Macro|Security...select Medium|OK|Close|Open the workbook
again|Click the "Enable Macros" button on the "Security Warning"
dialog.

To get the code in place...

1. Copy code below...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, _
Range("C2:C" & Rows.Count)) Is Nothing Then
With Application
..EnableEvents = False
..ScreenUpdating = False
End With
Dim rngCell As Range
For Each rngCell In Intersect(Target, _
Range("C2:C" & Rows.Count))
On Error Resume Next
rngCell.Value = rngCell.Value + _
rngCell.Offset(0, -1).Value
Next rngCell
Application.EnableEvents = True
End If
End Sub

2. Right click the worksheets sheet tab then select "View Code" from
the popup
3. Paste the code into the worksheet module that appears.
4. Return to Excel by pressing Alt + F11 or going File|Close and Return
to Microsoft Excel.


Ken Johnson
 
K

Ken Johnson

Hi Ken,

If my interpretation of your question is correct I would like to add
that it is not a very good way to handle your data.
Say you wish to correct a column B value after the column C value had
already been added and therefore converted to the sum of the B and C
values. Excel can't then revise the new column C value because there is
no record of the original value typed in by the user. If the user
neglects to reenter the original column C value (I assume stored on
Paper or some other medium external to Excel) after revising a column B
value the worksheet will contain an error. The only solution to that
would be to prompt the user to reenter the original column C value if
the corresponding B cell was changed while the C cell is not blank.
All too complicated.

The following code changes incorrect C values to "Re-Enter" and a
MsgBox prompts the user to re-enter those C values that would be
incorrect because of a review of the B value...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, _
Range("C2:C" & Rows.Count)) Is Nothing Then
With Application
..EnableEvents = False
..ScreenUpdating = False
End With
Dim rngCell As Range
For Each rngCell In Intersect(Target, _
Range("C2:C" & Rows.Count))
On Error Resume Next
rngCell.Value = rngCell.Value + _
rngCell.Offset(0, -1).Value
Next rngCell
End If
If Not Intersect(Target, _
Range("B2:B" & Rows.Count)) Is Nothing Then
Dim blnReEnter As Boolean
For Each rngCell In Intersect(Target, _
Range("B2:B" & Rows.Count)).Offset(0, 1)
If rngCell.Value <> "" Then
rngCell.Value = "Re-Enter"
blnReEnter = True
End If
Next rngCell
End If
If blnReEnter Then MsgBox _
"One or more Column C values need to be re-entered"
blnReEnter = False
Application.EnableEvents = True
End Sub

However, you should instead have separate columns for the original C
values and the sum of B and C values, removing the need for any code.

Ken Johnson
 
Top