Add an IF statement that encloses the formula you've already written. Assuming the result is in one cell, called CELLREF for purposes of writing this for you:
=If(CELLREF>0,CELLREF,(------INSERT YOUR FORMULA ABOVE------),0)
Thank you for rating FixYa!
What is the address used for what we called CELLREF? It should be the cell that contains yesterday's result. In other words, you would have one cell with yesterday's calculation result (CELLREF), and the formula as written and modified above would reference that cell. Obviously, if you reference the cell you're in when writing a formula, you will get a circular reference, as a cell can't reference itself to calculate...
Perhaps if I could understand what you are trying to accomplish it would help. Are you trying to get to a running total? I would be happy to go into a chat with you or to look at some spreadsheet (even test data, e.g., by e-mail) in order to help you write the formula. This is definitely attainable, we just need to clarify what you are trying to achieve. I am certain we can FixYa, as Excel formulas are one of my most prominent and serious areas. Let's communicate and get it done!
I would assume that the extra comma after H5 in the formula above is not really there in your sheet; otherwise the formula wouldn't work - ? Macro written for you, with a couple cells added to be able to more completely follow. The added cells could be placed wherever you like, and the macro would still work so long as the cell names are retained appropriately. So, OK, here we go:
Cells are named as follows (to be able to trace and understand more easily in working with them):
H5-DateToday // H6-DateRetain // D54-TotalCurrent // C54-TotalToday // B54-TotalRetain
-----------------------------------
Formulas in above:
H5: Manually input current date (today formula could be used if you do this every day).
H6: Blank; left to retain the contents of macro after running, so that date of today will be there tomorrow...
D54: =IF(TotalToday>0,TotalToday,TotalRetain)
C54: =IF(AND($D$46-$D$51>0,TODAY()=DateToday),$D$46-$D$51,0)
B54: Blank; left to retain the contents of TotalCurrent to carry forward the amount for comparison the following day.
---------------------------------------
In effect, what this will do is what you have asked, and I have tested it about 25 different ways - it always works. The total from today's calculation is copied to the TotalRetain field and the date is copied to the DateRetain field - so you know what has been carried forward and held constant for comparison. The macro must be run before closing it out for the day, so that it is set for the next day.
-------------------------------
Macro (which I named Retain - choose what you like):
Sub Retain()
'
' Retain Macro
' Macro recorded 04/25/2008
'
' Select DateToday and copy to DateRetain
Range("DateToday").Select
Selection.Copy
Range("DateRetain").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Select TotalCurrent and copy to TotalRetain
Range("TotalCurrent").Select
Selection.Copy
Range("TotalRetain").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
----------------------------------------
The macro will need to be pasted into the VBA window, and can then be run from within your worksheet, typically I would think prior to closing it. The formulas will work every day upon opening, assuming the macro was run when it was last used.
We should not definitely be at a FixYa rating, don't you think?!
:-) Thanks and good luck!
Sorry, pim99, I just looked at this again and noticed the typo at the end, it should read "We should now (not "not") definitely..."
I am just hoping that since I did so much work to give you an absolutely workable solution, that you might be able to see your way clear to a FixYa rating. Of course, I will continue to help if you have any need for more assistance in how to accomplish the items I wrote for you. Thanks for rating FixYa!
Fabulous. Just let me know if there is any further need for help!
Did you name the cells as I indicated and set those up? Did you copy the macro code exactly into the VBA window, or how did you get it into Excel?
pim99:
A couple of thoughts occurred to me through the night:
1. The formulas I wrote and used in my test scenario obviously were all on the same sheet, and therefore did not reference another sheet (as yours indicates a sheet named SummaryTotals). If this is a sheet separate from where the formulas I gave you reside, that needs to be added in to the cell references.
2. What exactly do you mean the system won't recognize the macro? Do you go to Macro - Run, and it's not listed? If so, I would guess that the macro was not placed into the proper VBA window -- OR
3. What security levels are set in re macros? If the security settings are too high, no macros are permitted to run by Excel.
Please let me know, and if there is a way for me to help to get it into the sheet directly, I would be happy to. You could also go in and record the macro directly, following the same steps after the new cells are set up. Waiting to hear back...
By the way, I obviously don't know your particular levels of expertise and so forth, but if you need help understanding the usage of functions, hyperlinks, macros, etc., I will be happy to help, but this will take time. Hyperlinks hop to another spot, basically, and do not perform calculations or make decisions. Functions perform specified calculations, and custom functions are also written in VBA. Macros are written in, and modified/edited in VBA as well, and VBA is a long and specialized area of its own. The easiest way to get a macro into a workbook, and have it do what you intend, is to record the macro from within the workbook. What I did was to do that for you, edited to make it more understandable and easy to follow, tested it some 25+ times, and then put it out there for you. So, I think at this point, we just need to determine why it isn't coming up, and this is just a process of elimination. It DOES work!
We could likely accomplish it with formulas, but it would definitely require adding more cells containing formulas that would basically just serve as "holding containers" and make the process much more complex. This would also very likely end up resulting in changing to manual calculation (rarely a good idea) and/or several steps becoming manual processes. All in all the cleanest, easiest way to follow the process is using the macro with the few added cells we have placed. Again, it is difficult without the spreadsheet, and as an "outsider looking in", but I spent a good amount of time on this, and I truly believe it is the best solution. Everything I could come up with involving formulas only either would not work, or was far too cumbersome, and had the disadvantages mentioned earlier. Although not much is really impossible, if the macro works, I would suggest to go with it. I will continue to think about this, and the only thing running around in my mind right now is a way to possibly use the "DateRetain" field as a qualifier in a nested IF statement. Let me know - smartsolve
Within the context of your needs, it might be better to have the macro run automatically when the file is opened - what do you think? This would do away with the concern re users forgetting, and would be no different from a function that operates automatically when the workbook opens. I think that even if we could get formulas to do it (and maybe we could with a bunch more time and added cells...), it would definitely be a much more convoluted process and weigh it down. Pleas look at this reference, and let me know before I take the time to write another macro for you. (I think would be better to use the open event VBA macro as opposed to autorun recording, if possible.)
http://office.microsoft.com/en-us/excel/...
×
267 views
Usually answered in minutes!
This caused a circular reference error, but thanks for looking. pim99
Hi Smartsolve,
The following formula looks at two values from today and produces a result, and records the results in the cell. What I need is that value to be kept in the cell and not zeroised the next time the formula runs. I f you leave the else 0 out it causes an error. What is really needed here is some code or formula to leave the cell alone if it has a value greater than zero in it. In this respect it would not matter if the underlying formula was removed as long as the value remained in tact.
Hope this clarifies the problem further.
pim99
It looks great. I have not thoroughly tested it yet, but thanks for all your help.
I have tried to run this macro but the system will not even recognize it exists, I expect because it is a macro that needs to be hyperlinked to something, and is not a function. Any suggestions
Smartsolve,
Thanks for all your help. What you did was fine as a macro that could be hyperlinked. This loaded up fine as a macro in VBA. What I was was really after was a piece of code (at the end of the formula above to replace the else '0' whereby the resultant values would stay intact and would not be overwritten with the else '0' when the formula ran the next day. I have tried various scenarios but most have resulted in circular references. Not sure it can be done!
pim99
Thanks for looking at it again. Although the macro seems the only viable option I really wanted to move away from this as it was implicit on the user actioning it, and users being what they are are prone to forget. It looks as if I will have to emphasize its importance on a daily basis instead of the daily automated save that I was hoping for.
Anyway thanks again for looking into this.
Regards,
pim99
The problem here is that I could have 365 days of individual data that I want captured and saved. In this respect I would not want the daily data overwritten. The only way to achieve this is to get the user to action a dedicated copy/paste macro at the end of the day. Of course this has the additional overhead of having to create 365/366 macros. This solution I regard as the sledgehammer approach, but in the absense of any formula code to get round this problem the daily macro seems to be the only solution. Unless anything more effective comes along I will continue to create the macros in VBA until reaching the 365/366 macro requirements and hyperlinking them to the day's calculated values.
Regards,
Mike Perry
i am a reasonable novice at this but i am trying to save an excel sheet via a macro, i want to save the sheet but want the name of the file to be the content of an individual cell ie, the date inputed in a cell or a name in a cell, anyone help??
×