Solved

Get Summary

Posted on 2013-06-20
9
235 Views
Last Modified: 2013-06-28
Hello,

Weird thing that I am dealing with really. But no options that I can see. OK, there are multiple sections in a sheet for example – blue, green and red.
Each section has some description and text. Now users can fill in any cell they want in that section.
Need – somehow, someway I need this summary kind of idea.
If the user makes a changes or enters something is a section, then in the summary sheet – it shows up like the section and the rows where the changes was made. See summary sheet for example.

Note there can be merged cells in the sections…

Thank you
EEQuestion66.xlsx
0
Comment
Question by:Rayne
  • 5
  • 3
9 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 39265447
I sometimes use the method in the attached "logfile".

Any changes made to sheets 1 or 2 are written to a txt file in the same place as the logfile.
(the saveloaction can be modified easily enough)
Then using the other code in the workbook (CSV import) a querytable is created of the data which represents the changes (I would do this on the Summary sheet to prevent the changes going to the logfile).
Once the querytable is there you need not re-create it, simply right click refresh.

With a few changes you could have the log determine the area the change is made and report this into the log.
this would allow multiple workbooks feeding into a single txt file.

I am not sure if this is the direction you would like to take, is up to you to see if you think it could be applied to your needs.
LogFile.xlsm
0
 

Author Comment

by:Rayne
ID: 39266963
Hello The Barman,
Thank you so much for your help. Actually I would prefer that if changes are saved on the summary sheet within the same workbook. That’s folks requirement.
Thank You
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39269176
Rayne,
Your work flow is a little murky. Please follow my train of thought and clarify:-
Since you don't want a log you want a sheet comparison. You can do that at a click of a button or at some event, like when you open the workbook or activate the worksheet.
But then you want to save that status and I don't see a good reason for that.
If the differences remain at the next click or event they would be included in a new report made at that time (plus any that may have been added since).
If the differences have been removed they would no longer show.
But if you wanted to show what had been changed you would need a log.
So, why save the worksheet?
In fact, if you use a button rather than an event to run the code you would have a saved copy of the changes. The problem would be that it would no longer be true until you run the code again, and at that time the saved version would be lost.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 

Author Comment

by:Rayne
ID: 39269945
Hello Faustulus,

Please see my comments (>>)

Your work flow is a little murky. Please follow my train of thought and clarify:-
>>I will :)

Since you don't want a log you want a sheet comparison. You can do that at a click of a button or at some event, like when you open the workbook or activate the worksheet.
But then you want to save that status and I don't see a good reason for that.
>>Sorry, I take it back, I don’t want to save the change sheet

If the differences remain at the next click or event they would be included in a new report made at that time (plus any that may have been added since).
If the differences have been removed they would no longer show.
>>Yes, that works


But if you wanted to show what had been changed you would need a log.
>>I don’t want to log.

If  you could guide me what could be done, if change sheet is updated dynamically showing only the changes at any instant of time. That could be activated via button.
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 39270232
You would like the first visible sheet to be activated when you open the workbook.
Please paste the following code in the ThisWorkbook code module of each workbook where you wish this action to take place. You will need to save the workbook in xlsm format, as macro-enabled.
Option Explicit

Private Sub Workbook_Open()
    
    Dim Idx As Long
    
    Do
        Idx = Idx + 1
        On Error Resume Next
        Sheets(Idx).Activate
    Loop While Err
End Sub

Open in new window

Regarding the record of changes you are thinking of please look at the attached draft. To quickly familiarize you with the layout, there is a short code on each sheet on which you wish to track changes. You can copy and paste this to the sheets in your own project.  And there is the standard code module ChangeMan which can be dragged into your project or copied/pasted. The code module ThisWorkbook has the above code, but that is basically unrelated.
At the top of the code in ChangeMan you have two constants which you can modify as required. 'DateStamp' defines a cell on the copy where a date will be written. It will be better to have this cell not so far away. In fact, you may wish to have it in plain sight.
'CopyID' defines a word you append to a sheet name to differentiate it from the original sheet of which it is a copy. The leading blank is part of the design.

Below these two constant declarations you will find the Sub 'ResetApplication'. If the program ever crashes for any reason Excel will probably become unresponsive. Please run this procedure to reset it. Place the cursor anywhere within the procedure and press F5.

Now, when a change is made on a designated sheet a copy of that sheet is created and marked with the date. If such a copy already exists the date will be checked, and if it isn't today's date the existing copy will be deleted.
If (after the above or before) there is no copy such a copy will be made and hidden. No changes are traced at any time.

Whenever you wish to see the changes run the Sub 'ShowChanges'. It is available from the Developer Tab -> Macros list. Select it there and click Run. Later you can have a button for it or a keyboard shortcut.
At this time all cells in the copy which were not changed will be deleted resulting in those that were changed remaining. The sheet is made visible and activated. You can hide it again or delete it after you are done.

You may like to retain some basic structure of the copy. This would require some generic way of identifying the parts you don't want deleted. Observe that it would also be possible to show the original version rather than the changes because the copy was made before the changes.

You say that you have zillions of worksheets. Having an extra copy of each may prove to be a lot of luggage. It would be possible to delete all added worksheets when the workbook is closed, or some of them, depending upon the date. You could also manage the date differently, for example, keeping changes for a week instead of a day or asking before deleting. There are lots of things you could do. Therefore I haven't done any of them. Let me know what you would like to have.
EXX-130623-Show-Changes.xlsm
0
 

Author Comment

by:Rayne
ID: 39270263
Hello Faustulus,

How can I thank you? You zoomed on what I was thinking. There might be some new requirements later but thank you a million :)
0
 

Author Comment

by:Rayne
ID: 39285829
Hello the Barman,

Here is a follow up question for you - I liked your solution
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28171143.html
0
 

Author Comment

by:Rayne
ID: 39285836
This is just awesome solution, please reply :)
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39286275
Rayne,
You are taking two dead threads trying to get a live response. How about asking a question?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

790 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