Is there a way to set a column to automatically replace its present formula with its calculated value…based on a specific criteria?
What I’m looking for specifically is to be able to say IF Cell “X” > 1, then ditch the formula and present just the value only in Cell “Y”.
Is this possible? Thank you.
Are you talking about Excel, or a similar spreadsheet? Then, yes, you can:
=IF(X1>1,Y1,{formula})
where {formula} marks where the present formula would go.
My first assumption is that you are asking about Excel. Giles answer is 100% right, if it answers the question that was intended. Is that what you wanted?
Just in case you’re asking something else, can we be more specific about the question?
Let’s say you have a value in cell A1 (better than saying “cell X” since it’s an actual Excel address). If the value in A1 is >1, you want to ditch a formula. What cell is that formula in?
Do you mean that if a formula in a cell gives a result with a value >1, you want that formula to magically go away and that cell should just have the value of the result? If so, you can do it, but only if you program it with VBA.
Thank you for the responses Glies and CookingWithGas. I think you’re right and I’ve not made myself clear enough. I’ll try again using better terms this time…sorry.
My first mistake in my previous question…yes this is an Excel spreadsheet I’m working with.
I think this may be more specific. A1 has a formula in it, but I’d like the formula in A1 to stop calculating and just show it’s value from the point when B1>1 (which means until I manually enter another value into cell B1.
Right now, after I manually enter an amount into B1, I have to go back to A1 and manually copy and paste “value only” into A1 in order to lose the formula. I need the formula in A1 to stop calculating once I’ve entered a final number into B1, I print it out at the end of the week. If I don’t do this then A1 could recalculate based on other cells it’s linked too, and at the end of the week when I print it, the numbers do not add up as they should for the end of the week.
For instance A1=sum(Z1+AA1), this sum right now shows A1’s value as “$256.00”. I write a check for the commission of $256.00 and enter the check #1981 into cell B1. Now cell A1 has to remain “$256.00” or I can’t depend on this record.
If why I need this makes no sensse right now…consider that sometimes I’ve already paid an employee their commission check and the customer is later talked into a service upgrade which makes the commission change in my main spreadsheet, but I don’t want it to change on THIS one. Don’t worry. The salespersons still get their commission for the upgrade, but it calculates on a seperate “suppliments” spreadsheet, where a new check # can be entered once they are paid for the upgrade.
I hope you can make sense of me this time. Thanks so much for any advise you may have for me.
OK. Let me paraphrase.
You have a cell A1 with a formula in it. This formula depends on multiple cells. When the value of B1 becomes >1, I want A1’s value to freeze, instead of using the formula, which could cause subsequent undesirable updates.
My first impression is that you need a better design for your spreadsheet. But that’s something that I can’t really advise you on without seeing it.
If I’m wrong about that (and it wouldn’t be the first time), then to do exactly what you describe cannot be done with Excel features and requires VBA. I am assuming you are not familiar with programming Excel using VBA. VBA is a programming language that can be used to write macros for Microsoft Office applications. Every MS Office application acts as a runtime environment for the language so you don’t have to install anything else. If you have Excel, you have everything you need.
If you are in Excel, hit ALT+F11. That will bring up another window which is the VBA development environment. That’s where you write the code. On the left you would see a folder structure which includes your current Excel workbook. Under that you would see a folder for each worksheet. If you double click on the folder icon for the sheet where your check numbers and amounts are located, you will see a blank page for code come up on the windowpane to the right. You would enter code that looks something like this:
' This Sub is automatically called when there is any change on this worksheet.
' If the change causes the value of B1 to exceed 1, then the value of A1 replaces
' the formula in A1.
Public Sub Worksheet_Change(ByVal Target As Range)
' Check to see if the change occurred in cell D1
If Target.Address = Range("B1").Address Then
' Check to see if the change caused the value to go above 1
If Target.Value > 1 Then
' Assign the value of A1 back to the cell as a value
' This is effectively the same as copy then "paste as value"
Range("A1").Value = Range("A1").Value
End If
End If
End Sub
This is a simplified example because you have many check numbers and each one corresponds to a different check amount. But this gives you the general idea.
I can help further if you want to go in this direction. It will probably require sending files back and forth through email.
Actually, this can be done without VBA, sort of, though it’s more of a hack than using a script is. (And I agree that this probably means a redesign would help.) Here’s a sample sheet:
A B C D
1 values lock total? total locked? total
2 10 0 =OR(B2=1,C2) =IF(C2,D2,SUM(A2:A10))
3 20
4 30
5 40
6 50
Note that C2 and D2 contain circular references. By default Excel will complain about these because these can make efficient calculation more difficult (and potentially impossible) but you can change Excel’s behavior to allow them. Go to Tools > Options > Calculation and check the “Iteration” box (that’s for Excel 2003; in Excel 2007, it’s “Enable iterative calculation” under “Formulas” in the options). This tells Excel to repeatedly recalculate circular references until either the values don’t change (much) or you reach the iteration limit. In our case this will only involve a couple rounds of iteration so it’s not too bad.
The values to be summed are in A2:A10; the total appears in D2. Initially C2 evaluates to FALSE, so the total will be updated until the first time B2 is set to 1. After this C2 will remain TRUE even after B2 is changed back to 0, and further changes to the values will not affect the now-locked total. (To unlock the total you have to explicitly reset the lock cell C2, e.g. by hitting <F2> <enter> on it.)
In your case you want a slightly different test than “B2=1”, maybe just “NOT(ISBLANK(B2))”. You may also want to preserve other values than just the total; you can do this using the same pattern as for D2.
Thanks for the responses, Cooking With Gas and Omphaloskeptic.
Cooking With Gas…you would be correct that I’m not skilled with macros. I understand your concern about my spreadsheets, but will assure you that the particular sheet I’m applying this to is not set up to calculate anything, but is simply linked to the main page so I don’t have to re-enter the information I need to show on this “Commission Report”. I would rather copy and paste"value only" into the same rows, than to have to enter all this in over and over again. Once it’s on the page though, it needs not to reflect the changes that are happeing…and need to continue happening in my Master Workbook.
Omphaloskeptic…I think I get the jist, but am not sure this would create the result I’m looking for. I’m going to have to play with it and see if I can do anything with it before I’ll know for sure.
Again…thanks to both of you for your advise here.
It may be possible to simplify the example above, depending on exactly what behavior you need. It sounds like you might just need the value in the Total field to be held only if the Check Number field is filled in (so that if that field is cleared, the Total field can update itself again). If that’s true you don’t need the extra Locked cell, so you can do something like this (cells moved around to follow the convention in your post):
A B
1 =IF(ISBLANK(B1),SUM(A2:A10),A1)
2 10 ^^^ check number goes in B1 ^^^
3 20
4 30
5 40
6 50
This updates the total only as long as B1 is empty. Fill in a value in B1 and A1 won’t change until you clear B1 again.
how do you use this code for the whole columns A and B
I found this to be exactly what I need for my project…but since my VBA is really at level 0, how would i apply this to the entire column instead of just 1 cell?
I think there might be a fundamental design concept issue - you’re dealing with a collection of transactions that happen over a span of time, but it sounds like you are dealing with them by changing a single stored value.
You really ought to be putting all those transactions into a list, and adding new transactions, or adjustments to existing transactions on the bottom of the list. If the list has a column for the effective date of each transaction, then you can conditionally sum them by date range - and if you don’t want something to appear as a backdated change in last month’s total, you just don’t give it a last month date.
This is a zombie thread, so unless you have an answer to arturo’s specific additional question, there is probably not much point in giving advice.
Do you mean that instead of just checking for a change in B1, you want to check for a change anywhere in column B? See revised code below.
' This Sub is automatically called when there is any change on this worksheet.
' If the change is in column B and causes the value to exceed 1, then the value of A in the same row replaces
' the formula in that cell.
Public Sub Worksheet_Change(ByVal Target As Range)
' Check to see if the change occurred in column B
If Target.Column = 2 Then
' Check to see if the change caused the value to go above 1
If Target.Value > 1 Then
' Assign the value of A1 back to the cell as a value
' This is effectively the same as copy then "paste as value"
Cells(Target.Row,"A").Value = Cells(Target.Row,"A").Value
End If
End If
End Sub
This works almost perfectly. I mean the code does exactly what I need but when i drag and release column B (to copy paste all in the column with a same content, there is an error message “type mismatch”. Also, is there anyway so that the code " If Target.Column = 2" doesnt have to be case sensitive? i’m hardcoding it to “Shipped”
Thanks so much for taking the time to help me out here.
I’ve really got to start paying more attention to the dates :smack:
I wonder if there’s a greasemonkey script out there to help with this
There is an assumption in this code that only one cell will change. If you are changing multiple cells in one shot you need this change, which I have not tested. That is probably why you are getting the error.
' This Sub is automatically called when there is any change on this worksheet.
' If the change is in column B and causes the value to exceed 1, then the value of A in the same row replaces
' the formula in that cell.
Public Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range ' cell
For Each C In Target
' Check to see if the change occurred in column B
If C.Column = 2 Then
' Check to see if the change caused the value to go above 1
If C.Value > 1 Then
' Assign the value of A1 back to the cell as a value
' This is effectively the same as copy then "paste as value"
Cells(C.Row, "A").Value = Cells(C.Row, "A").Value
End If
End If
Next C
I don’t understand what you mean. There is nothing in this code that looks at text, so case sensitivity is a non sequitur. That line of code checks to see whether the cell that changed is in column B (which is column number 2). What are you hardcoding to “Shipped”?
If you still can’t get this working it would help to see your file. You can PM me to get my email address, or you can register at www.excelforum.com for free and attach a file there.
CookingWithGas I have a similar problem, tried using the above code but it is not working.
I have a workbook with multiple sheets, and i need this to work in just one of the worksheet(sheet name - ‘DATA’). I have a IF formula in column ‘V’ , in worksheet ‘DATA’.
Whenever the value of any cell in ‘V’ changes to > 1, I want the formula to be replaced by value. For example, if the value in cell ‘V26’ changes to ‘857’ (based on IF formula), then the IF formula in ‘V26’ needs to be replaced with value of ‘857’
I’m using below code, but it is not working.
Please help 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "DATA" Then Exit Sub
If Target.Column = 22 Then ' 22 is the column number for 'V'
If Target.Value > 1 Then
Cells(Target.Row, "V").Value = Cells(Target.Row, "V").Value
End If
End If
End Sub