Solved

Get Summary

Posted on 2013-06-20
9
231 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Rayne
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
This is just awesome solution, please reply :)
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
Rayne,
You are taking two dead threads trying to get a live response. How about asking a question?
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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 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

9 Experts available now in Live!

Get 1:1 Help Now