Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I leave the contents of a cell alone in an IF statement?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I leave the contents of a cell alone in an IF statement?
B1: =A1
B2: =B1+A2, Copy down as far as needed -- Kind regards, Niek Otten Microsoft MVP - Excel "Ken" wrote in message ... | 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I leave the contents of a cell alone in an IF statemen
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 "Niek Otten" wrote: B1: =A1 B2: =B1+A2, Copy down as far as needed -- Kind regards, Niek Otten Microsoft MVP - Excel "Ken" wrote in message ... | 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I leave the contents of a cell alone in an IF statement?
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Niek Otten" wrote in message ... B1: =A1 B2: =B1+A2, Copy down as far as needed -- Kind regards, Niek Otten Microsoft MVP - Excel "Ken" wrote in message ... | 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I leave the contents of a cell alone in an IF statemen
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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Niek Otten" wrote in message ... B1: =A1 B2: =B1+A2, Copy down as far as needed -- Kind regards, Niek Otten Microsoft MVP - Excel "Ken" wrote in message ... | 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I leave the contents of a cell alone in an IF statemen
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Ken" wrote in message ... 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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Niek Otten" wrote in message ... B1: =A1 B2: =B1+A2, Copy down as far as needed -- Kind regards, Niek Otten Microsoft MVP - Excel "Ken" wrote in message ... | 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I leave the contents of a cell alone in an IF statemen
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I leave the contents of a cell alone in an IF statemen
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas to copy contents of one cell in a worksheet to another ce | Excel Discussion (Misc queries) | |||
Selectively Clearing cell contents | Excel Worksheet Functions | |||
Returning Cell Value if someone deletes the contents of a cell | Excel Worksheet Functions | |||
How do I get a number or letter to represent cell contents? | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions |