Solved

"Freeze" the results in a table with a formula

Posted on 2011-03-10
20
347 Views
Last Modified: 2012-05-11
Dear Experts,
could you please suggest a "best solution" for the following requirement?
Maybe there is a built in command/function which I do not know, maybe this requires manual work (copy and past) maybe a simple macro on button push will solve it....

I have monthly data import in sheet1. I do not want to have 12 sheets, so I thought I have sheet1 called data-import and copy from another source once a month data to that place.
On sheet2 etc. I have reports which take and summaries respectively from that data by month.

The point is now that the results get lost from February as soon I will copy the data from March.
So I'm looking for  clever solution to "freeze" the data in the February column on sheet2 by button click on top of the column or by a formula or command. Actually I need to keep the values but need to get rid of the formula activity.
So next month I copy March data, Feb results will be kept in the table and March is added.

Currently I use the copy and paste value manual method to a second "result table" but I'm sure there is a better solution out.
thanks for suggestions and help
Nils









0
Comment
Question by:Petersburg1
  • 10
  • 9
20 Comments
 
LVL 3

Expert Comment

by:longtruong
ID: 35106140
Hello Petersburg1,

Could you please post a file which contains sample data to illustrate what you need?

Regards,

Long
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35106144
Dear Petersburg,

Would it help that with the save event you automaticaly copy and paste special values..

It could be made that the file is ready for A new month, or you have to click a button to refill formula's to where you need them?

Does any of this make sence?

KR

Eric
0
 

Author Comment

by:Petersburg1
ID: 35107590
Dear longtruong and Zwiekhorst.
I have attached the file. Hope it will help to find a solution.
Currently I have data from Feb in the import sheet and got the results. I need now to "freeze" the results even I delete the import data and get data for march in.
thanks

Paper-Usage-Tracking-NIK.zip
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35107738
Dear petersburg

This file holds a macro that will replace the formula in the printer sheet with its value when you save the workbook.

Only where the SUM > 0

if you need to do the same in other sheets to , the macro is easily adapted..
If you don't know how just ask me..

Kind regards

Eric
Paper-Usage-Tracking-NIK.xls
0
 

Author Comment

by:Petersburg1
ID: 35110067
Hi Eric,
Yes, I would need it for more sheets also in other files so I have to understand the code a bit better and I will be able to adapt it myself:
1. Where and how to add more sheets?
2. For i = 2 To 13 is the range right in row 4 right?
3. For j = 5 To 20 is the range of rows with the formula, right?

However, something seems not to work together with my already existing function....
It seems to be connected with the fact that I moved the Data Import sheet from the first position to the last position. Why is that? What need to be changed in the code where that previous and new function will work smoothly together?
And how to reset everything? I mean to loose data but have for each month calculation again active?
At the end of the year I would need it and also in between when I get a data update of a certain month based on some mistakes...there should be the possibility to "release" a frozen month and have new data...Maybe with a help of a helping line on top of each month? By default = C for calculate and f= for frozen. As soon I place a a c instead of an f and save, next time I open I will have lost the data but therefore can calculate again?
thanks
Nils



Sub valpst()
Dim i, j As Integer
Dim totl As Variant
Sheets("Printer-Report").Select
For i = 2 To 13
    If Cells(4, i) > 0 Then
        For j = 5 To 20
            Cells(j, i) = Cells(j, i).Value
        Next
    End If
Next
       
i = i
End Sub
0
 

Author Comment

by:Petersburg1
ID: 35110071
I increased the points...
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35125649
Dear Petersburg,

I didn't find the time to look at your responds in the weekend.. Will get to it now.

I wil try to answer your questions

1 : The macro now looks for a sheet named "Printer-Report" if there exists no sheet with that name it will give a error. If you want to use different sheetnames you can put them into variables and use the variable instead of the "Printer-Report")
2: Indeed it questions Cells(4,I) where the 4 stands for the row. it look for a sum that is > 0 so it knows there are values in the column.
3: and again you are right the 5 to 20 are the row numbers of wich the formula is to be transformed into a value.

The place for data import sheet is not important, I assumed you have always the Printer-Report to start from.

on how to reset averything, there are several possibilitys:

1: you could have a template somewhere with the page holding all formulas, then at years end delete sheet without formulas and copy in the sheet with formula's

2: make a macro that populates the fields with the formula..

your suggestion to put C and F are acceptable to, what would you say about putting a 0 in the sum would reset calculation?

I could help with this if you would need it just ask...
Kind regards

Eric
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35125746
Dear Petersburg,

I altered the code so that when saving the file it checks if the sum was zero'd that means the formula is gone a a real 0 was taped into that cell.
Then it will repopulate the column with the formula, even the zero'd sum..


Hope this and the above explanation helps you ..


Kind regads

Eric
Paper-Usage-Tracking-NIK.xls
0
 

Author Comment

by:Petersburg1
ID: 35127391
Hi Eric,
nice with the zero and your macro does work!
However I does not work in combination with my macro?!
1. When I open the file I get the message as before, asking if I want to delete the range at sheet1. Even I confirm nothing is deleted
2.If I press the "delete button" on that sheet it also does not work. It is as I would be in design mode. If I click to the delete button it reacts like in design mode and shows the button frame.
3. On the original excel file I have sent to you it also does not work but it works as soon you move the data import sheet back to the first position. Therefore my question:

A) Could you make both function work together?
B) Why "my marco" requires that the data import sheet is the first sheet? What to change where to have it any other place?
C) Why the button does not work as well?
D) Could you make your macro working for all three report sheets?

Thank you very much in advance for your help and patience with me.
Nils
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35128404
Hi Petersburg,
 A i will do this but I can send it this evening..

B     If CheckMe = vbYes Then ThisWorkbook.Sheets(1).Range(rng1.Address).ClearContents
 Change Sheet(1) to Sheets("Data-Import")  thisway it looks for the name and not for the internal number..

c: the button doed not work because it can not find the right sheet.

D: yes I could, will send the result this evening..
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Petersburg1
ID: 35130096
Eric,
I tried a bit but it does not work either this:
If CheckMe = vbYes Then ThisWorkbook("Data-Import").Range(rng1.Address).ClearContents

nor that way and some other variants...

If CheckMe = vbYes Then ThisWorkbook.("Data-Import").Range(rng1.Address).ClearContents

I get "Object doesn't support this property or method" (runtime error 438) back.
thanks
Nils
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35135419
Dear Petersburg,

Try this version.
Your problem lies in the fact you want to do it in the workbook open macro.
I placed it in the workbook modules and now it works.

I changed the macro so it works with all the other sheets, had to add sum to one though.
Paper-Usage-Tracking-NIK.xls
0
 

Author Comment

by:Petersburg1
ID: 35156144
Hi, sorry for late answer,,,,still had no time to check...will answer as soon as possible. Did not forget about you!
thanks
nils
0
 

Author Comment

by:Petersburg1
ID: 35313355
Dear Zwiekhorst:
I'm back from sick and vacation and found now time to finalize this task. I had a look to the file, understood how to fix the issue I had with the button to delete content too.

When I now copy new data to that file I get as result perfectly calculated as I need on the report sheets and they come without formula, only values. Actually all I need. In this way I can delete and copy next month and still keep the data from the previous month.

To recover the formula, I can actually just copy e.g. April back to March and so on....but I remember that you implemented something with the zero....however this seems not to work or I do or don't do something which is required to trigger that function?

I have attached a pdf file with screenshots for better understanding. E.g. I have deleted the code which was still in the sheet1 for the button and I have deleted a first empty row in the module1 which makes not different to the functionality right?

More question:
Code in the sheet does only work for that sheet? Code in the module is working via macro and/or button? Code in "this workbook" works for all sheets in that workbook? Right?

What I have to add/change if I want to add that functionality to one or several more report sheets in the near future? Could you send me just the code block I would have to add to the "this workbook"?
thanks
Nils












Excelfilescreenshots.pdf
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 500 total points
ID: 35313486
Dear Peterburg1,

Code in a sheet is not limited to the sheet itself, it can look , select or activate all other sheets available, it can even open another workbook. but it is limited for other functions.

Code in this workbook works like the code in a worksheet for all other sheets and workbook also, you just have to adress them.

The difference is that the workbook codepage hold macro that are triggered when something happens to the workbook (open, close, save, print etc.._) the worksheet codepage hold macro's that are triggered by worksheet related events, (Like change, activate, double click etc..)

So it is only the triggering of the code that changes .
I hope you understand this explanation.

Maro's that are triggered by other macro's, triggered manually (button or just run macro) should be in a module, (right click in VBA the workbook and choose INSERT, and then MODULE

this sub (Sub valpst()) should be placed in this module. you can cut and paste... calls stay the same.

You can add sheets like you see underneath,
the variant SHTS is holding sheetname and places where sum is where to start and where to finish..
So add the new sheets by shts(3,0) = new sheet



shts(0, 0) = "Printer-Report" 'Name of sheet
shts(0, 1) = 4 'Row that holds sum
shts(0, 2) = 2 'start column
shts(0, 3) = 13 'end column
shts(1, 0) = "Person & Printer" 'Name of sheet
shts(1, 1) = 5 'Row that holds sum
shts(1, 2) = 2 'start column
shts(1, 3) = 13 'end column
shts(2, 0) = "Person" 'Name of sheet
shts(2, 1) = 5 'Row that holds sum
shts(2, 2) = 2 'start column
shts(2, 3) = 13 'end column


kind regards

Eric
0
 

Author Comment

by:Petersburg1
ID: 35315960
Dear Eric,
thanks so much. This task with all my questions is already worse more then 500 points I guess.
Ready to give to you if possible,...

Your answer was very helpful, as it helps me to understand important basics. However still not clear is for me the function of the last version from you:
Is there a function, macro which does recover the formula or has this to be done manually? I'm asking because this was under discussion by setting a zero?! However this does not work or I do not know how to make it working....on the other side we can skip it for now...just let me know!

0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35320896
Hi Petersburg,

the reset to formula should work. Just zero the sum in the column head. If you then save and reopen the formul's are back in place.


Kind regards

Eric
0
 

Author Comment

by:Petersburg1
ID: 35320985
Dear Eric,
PERFECT. In terms of patience, work effort and time, this is worse more then 500 points! Whenever you will come to St. Petersburg, Russia, I will give you a special discount in our B&B :-) www.rentroom.ru
Best regards
Nils
0
 

Author Closing Comment

by:Petersburg1
ID: 35320987
Lot's of patience, very helpful, nice solution, very happy!!
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35321054
Dear Nils I will try to remember this if I will Travel to St Peterburg one day...


Thanks for the grade and the points..

Eric
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now