Solved Save Calculated Value as Static Entry

October 12, 2012 at 05:07:24
Specs: Windows 7
Hi Guys,
This is an Excel question.
I need a cell to calculate on a certain date and then save the value. In plain English it would look something like this;

IF Today is 1/11/12 then =C5. Save value in D5 and do nothing else.

I need to track progress on the value of a single cell over time.

Any help will be much appreciated.

Michael


See More: Save Calculated Value as Static Entry

Report •


✔ Best Answer
October 16, 2012 at 21:11:31
In the screen shot that you sent me, you appear to have monthly dates starting in K371 and continuing along that row. For this solution to work, those must be actual dates, e.g. 10/1/2012, formatted as Oct-12, Nov-12, etc. or however you wish. The key point is that they must be dates, not text.

1 - Right-Click the sheet tab and choose View Code to open the VBA Editor. You should see large empty pane on the right, and a pane named "Project - VBA Project" on the left. If you don't see the left hand pane, click View...Project Explorer to open it.

2- In the Project Explorer, you should a list of the Sheet names for the sheets in your workbook, along with a listing for This Workbook.

3 - Double click the words This Workbook to open a new pane on the right. The word "General" in the drop down at the top of the right hand pane should change to Workbook.

4 - Paste this code into the Workbook pane:

Private Sub Workbook_Open()
'Determine last column with value in Row 373
  lastDateCol = Cells(373, Columns.Count).End(xlToLeft).Column
'Check if the last day of current month is greater than
'the last day of the month for last date in Row 371
    If Application.WorksheetFunction.EoMonth(Date, 0) > _
       Application.WorksheetFunction.EoMonth(Cells(371, lastDateCol), 0) Then
'If it is, then it must be a new month.
'Put value from C355 in the next open cell in Row 373
        Cells(373, lastDateCol + 1) = Cells(355, 3)
    End If
End Sub

5 - Save the workbook as a macro enabled workbook (.xlsm)

Here's how's this should work:

Each time the workbook is opened, this code will run automatically. The code will first determines the last cell in Row 373 that contains data. e.g. in your screenshot, the last cell with data in Row 373 is L373.

The code will then compare the date in L371 with the system date by using the EoMonth (End Of Month) function which returns the last day of the month referenced by the function. If the last day of the month of the system date is greater than the last day of the month in Row 371 of the same column as the last piece of data in Row 373, then it must be the first of a new month or later. If it is, the code will put the value currently in C355 in the next cell in Row 373. For the rest of the month, the EoMonth function will return the same value for the system date as for the date in the last Column of Row 373, so no action will be taken. On the first day of the next month that the file is opened, the EoMonth value for the system date will be greater than the date above the last piece of data in Row 373 and the value from C355 will be copied into the next cell.

The only way for you to test this code prior to the first of next month is to close the file, change the system date of the computer to the next month and then open the file. Once you are satisfied that it works, you can erase the new value or just not save the file. Of course, you also have to remember to reset the system date to the correct date.

As written, the code will not work correctly if the file is not opened at least once a month. i.e. if the file is opened on Oct 29, and then not opened again until Dec 1, it is going to put the value from C355 in the column for Nov-12 since that cell will not be filled in yet. This could be fixed with a few more lines of code, but since you said that the file is opened almost every day, I didn't bother dealing with the issue of a skipped month.

You should test this code in a backup copy of your workbook in case things go terribly wrong.

Let me know how this works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 12, 2012 at 12:43:19
I don't really understand what you are trying to do.

Are you saying that you have a cell with a formula such as the one below and on 1/11/12 you want the value from C5 to become a fixed value in the cell with that formula?

=IF(TODAY()=DATEVALUE("1/11/2012"),C5,"")

Is that the only day you are checking or do you have a series of dates in different cells that all need to reflect the value of C5 based on the date in each cell?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 12, 2012 at 15:02:32
Hi mate. Thanks for the response.

Currently I don't have any formula as I don't know what it should be.

C5 is a summary of several activities which updates every time an entry is made.

What I need to do is to save the value of C5 on the first day of each month in a different cell (D5, E5, F5 etc) so that I can track progress. D5, E5, F5 is linked to a chart so it will plot the progress.

So yes I'm only checking on that day for the value in C5 to be saved in cell D5 but it will be a series of cells as each month the value of C5 needs to be saved in a different cell. Does that make sense?

Because the last entry is "" does your example =IF(TODAY()=DATEVALUE("1/11/2012"),C5,"") mean that it will save that value and not change on 2/11/12? If that is what it means then that is my answer.

I was thinking that I would have to do the furmula as many times (months) in as many cells for the duration I want to track. Or perhaps it could be one long formula saying something like 'If Today is 1/11/12 save in D5, If 1/12/12 save in E5, If 1/1/13 save in F5' etc.

What ever is easiest if fine.

Thanks again for your help.


Report •

#3
October 12, 2012 at 16:00:52
Let's take this in pieces...

re: Because the last entry is "" does your example =IF(TODAY()=DATEVALUE("1/11/2012"),C5,"") mean that it will save that value and not change on 2/11/12?

No. The "" is the value_if_false argument for the IF statement. It means that if today's date is not "1/11/2012” then the formula will display an empty cell. "" basically means "nothing".

Think of it this way: A value enclosed in double quote is seen by Excel as text.

e.g. =IF(A1=5, "Five", "Not Five")

This will return either Five or Not Five based on the value in A1. If you replace the letters between the quotes with nothing then the cell will display nothing.

e.g. =IF(A1=5, "Five", "")

This will either display Five or an empty cell.

re: Or perhaps it could be one long formula saying something like 'If Today is 1/11/12 save in D5, If 1/12/12 save in E5, If 1/1/13 save in F5' etc.

A formula can not tell Excel to "save" anything in another cell. A formula can only return a value in the cell in which the formula resides. A formula cannot even save it's own result as a static number if that result is based on something that changes. Only VBA (Visual Basic for Application) also known as a "macro", can remove a formula and replace it with the result that the formula produced. Well, of course the user could do that also, but I assume we are trying to make this automatic.

OK, all of this is just an explanation of my formula as well as letting you know that a formula by itself is not going to get you what you want. It is going to take either a formula along with some VBA or some VBA by itself.

Before I worked on the code that you will require, I need to know if a VBA solution is going to be OK. You will need to set your security settings within Excel in order to allow macros to work as will anyone else who will need the code to work in the workbook.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 12, 2012 at 22:49:35
Yes a VBA/Macro solution is certainly fine as I need to make this automatic and in the process I guess I will learn how VBA works.

Many thanks for your time and help in all this. I've been searching for this about three weeks and you seem to be the only person that knows what is actually required.


Report •

#5
October 13, 2012 at 06:26:38
You said that you need to save the value in C5 on the first day of every month.

Does the file get opened on the first day of every month? If not, would saving the value the first time the file was opened on or after the first day of the month work?

If the file is opened on e.g. 10/1/2012 at 10:00 AM and the value saved, will it change again that day? If it does, do you want the latest change of the day or the earliest?

The issue is that the value can not be changed while the file is just sitting on a disk. Something has to happen within the file for the action to occur. Simply opening and closing the file would be enough, but I need to know a little about you use the file and what are the parameters around when the value can be saved.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 13, 2012 at 09:28:47
The workbook is used on a daily basis during the working week so there is a good chance that it will be opened on the first day of the month as well.

The only exception would be if the first day falls on a Holiday or weekend. Saving the value the first time it is opened on the first day of the month (or the first time it is opened after the first day) would indeed work. Once it's updated there is no need to do anything more until the first of the following month.

As I mentioned before, the cell being monitored is a summary of various activities which get updated frequently (sometimes daily) so the cell value will be increasing with each entry. I need to be able to track progress from month to month which will be plotted on a graph. That is why I need to save the value of the cell so it will remain in the graph and not go back to zero the following day. I can send you a screenshot of the actual page if that would help. I just don't know how I can do that here.


Report •

#7
October 13, 2012 at 16:57:46
Check your Private Messages for an email address you can send the screen shot to.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
October 16, 2012 at 21:11:31
✔ Best Answer
In the screen shot that you sent me, you appear to have monthly dates starting in K371 and continuing along that row. For this solution to work, those must be actual dates, e.g. 10/1/2012, formatted as Oct-12, Nov-12, etc. or however you wish. The key point is that they must be dates, not text.

1 - Right-Click the sheet tab and choose View Code to open the VBA Editor. You should see large empty pane on the right, and a pane named "Project - VBA Project" on the left. If you don't see the left hand pane, click View...Project Explorer to open it.

2- In the Project Explorer, you should a list of the Sheet names for the sheets in your workbook, along with a listing for This Workbook.

3 - Double click the words This Workbook to open a new pane on the right. The word "General" in the drop down at the top of the right hand pane should change to Workbook.

4 - Paste this code into the Workbook pane:

Private Sub Workbook_Open()
'Determine last column with value in Row 373
  lastDateCol = Cells(373, Columns.Count).End(xlToLeft).Column
'Check if the last day of current month is greater than
'the last day of the month for last date in Row 371
    If Application.WorksheetFunction.EoMonth(Date, 0) > _
       Application.WorksheetFunction.EoMonth(Cells(371, lastDateCol), 0) Then
'If it is, then it must be a new month.
'Put value from C355 in the next open cell in Row 373
        Cells(373, lastDateCol + 1) = Cells(355, 3)
    End If
End Sub

5 - Save the workbook as a macro enabled workbook (.xlsm)

Here's how's this should work:

Each time the workbook is opened, this code will run automatically. The code will first determines the last cell in Row 373 that contains data. e.g. in your screenshot, the last cell with data in Row 373 is L373.

The code will then compare the date in L371 with the system date by using the EoMonth (End Of Month) function which returns the last day of the month referenced by the function. If the last day of the month of the system date is greater than the last day of the month in Row 371 of the same column as the last piece of data in Row 373, then it must be the first of a new month or later. If it is, the code will put the value currently in C355 in the next cell in Row 373. For the rest of the month, the EoMonth function will return the same value for the system date as for the date in the last Column of Row 373, so no action will be taken. On the first day of the next month that the file is opened, the EoMonth value for the system date will be greater than the date above the last piece of data in Row 373 and the value from C355 will be copied into the next cell.

The only way for you to test this code prior to the first of next month is to close the file, change the system date of the computer to the next month and then open the file. Once you are satisfied that it works, you can erase the new value or just not save the file. Of course, you also have to remember to reset the system date to the correct date.

As written, the code will not work correctly if the file is not opened at least once a month. i.e. if the file is opened on Oct 29, and then not opened again until Dec 1, it is going to put the value from C355 in the column for Nov-12 since that cell will not be filled in yet. This could be fixed with a few more lines of code, but since you said that the file is opened almost every day, I didn't bother dealing with the issue of a skipped month.

You should test this code in a backup copy of your workbook in case things go terribly wrong.

Let me know how this works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
October 17, 2012 at 09:25:21
Man that works perfectly, thank you so much for all your help!! If we ever happen to be in the same city (or country) I will buy you a beer :-)

Report •

#10
October 17, 2012 at 09:54:33
I'm glad it worked for you.

Let me know when you're in the states!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
November 10, 2012 at 11:18:19
Ooops.... it worked on the 1st but reverted back to empty on the following day. Any ideas?

Report •

#12
November 10, 2012 at 14:56:04
Did you save the file after it updated?

This line puts an static value in the cell and there is nothing in the code that would clear the cell afterwards.

Cells(373, lastDateCol + 1) = Cells(355, 3)

Keep in mind that the value is put in the cell as soon as the file is opened. Therefore the file has changed and must be saved to retain the value. A line of code could be added at the end of the macro to save the file. e.g. Open, Check to see if the file needs to be updated, update if yes, don't if no, Save it.

Other than that, I can't think of a reason that the cell would be cleared, but I was working off of a screen shot, not with the actual file. If there is something else going on in the spreadsheet that I don't know about, all bets are off.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question