[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

"Freeze" the results in a table with a formula

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
Petersburg1
Asked:
Petersburg1
  • 10
  • 9
1 Solution
 
longtruongCommented:
Hello Petersburg1,

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

Regards,

Long
0
 
Eric ZwiekhorstCommented:
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
 
Petersburg1Author Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Eric ZwiekhorstCommented:
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
 
Petersburg1Author Commented:
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
 
Petersburg1Author Commented:
I increased the points...
0
 
Eric ZwiekhorstCommented:
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
 
Eric ZwiekhorstCommented:
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
 
Petersburg1Author Commented:
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
 
Eric ZwiekhorstCommented:
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
 
Petersburg1Author Commented:
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
 
Eric ZwiekhorstCommented:
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
 
Petersburg1Author Commented:
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
 
Petersburg1Author Commented:
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
 
Eric ZwiekhorstCommented:
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
 
Petersburg1Author Commented:
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
 
Eric ZwiekhorstCommented:
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
 
Petersburg1Author Commented:
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
 
Petersburg1Author Commented:
Lot's of patience, very helpful, nice solution, very happy!!
0
 
Eric ZwiekhorstCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now