Creating a “Summary of Changes” tab as a log of modifications

[Nurseryboy]

Hello,

I have a question/dilemma that I need a little guidance on (no Macros or VBA please). I’m trying to figure out what the best way to do this.

I need to create a “Summary of Changes” tab in one of my spreadsheets. Here are the details…

I had originally posted a question (that was Solved) about how to see any modifications in a large table (see http://www.excelforum.com/excel-form...ml#post4254910). All of the parameters for the overall workbook are in that previous post.

Now, I need to create a “Summary of Changes” that logs in all of the changes across ALL the sheets in the workbook with which day (or which tab) that modification was made (see attached sample sheet with the “SUMMARY OF CHANGES (NEEDED)” tab is the one that’s needed). Each day, there may be 5 – 50 modifications and I need a log of each one on one tab. By seeing which products are modified more frequently will allow me to better analyze the pricing structure for those products.

Please let me know any of your thoughts. Thank you very much in advance.

Yury

[igoodable]

Here's a radical proposal for you. You only need two tabs: Report and Data

On the DATA tab, you continuously add data for each day you have a price change. For example, if you have a price change tomorrow, create a completely new record for the change and have the date to identify the date it change.

On your Report page, create a pivot table based on the data. Hit refresh each time you add data and use slicers to filter what you need. It's quick, simple and efficient. You can see all items with a price change on a single click of a button.

See example attached.

[Nurseryboy]

Hi igoodable,

Thank you very much for responding with your suggestion...this is why I like to have "fresh eyes" on something that I've been doing for a long time . In looking over your suggestion, it occurred to me that all this time, I've had many more steps (and files/tabs) than I needed. I have been saving each set of changes in its own tab "for possible future error checking", but in all the years that I've been doing this I have never needed to go back to a previous change. Thus, your idea of completely simplifying the process made me think of how the whole thing can be more streamlined.

Although I do have to add a couple of more tabs, something like "Today's Changes" and "Existing Data", I can certainly use the DATA and Report tabs that you suggested (with an extra steps of coding each line with the modification date and copying that line to the DATA tab and then Refresh the Pivot Table with the updated info).

Thank you very much for the idea...it was a new perspective that I hadn't thought about. I'll certainly use your solution!

I just have one quick question for you on your Pivot Table. In the Report Tab, in the Pivot Table, how did you get Item and Listing on the same Row, Row 6 (within the Pivot Table, highlighted in Yellow)? I tried to do this in a new tab, "Test Pivot", and Excel puts these two fields on different Rows (as a Subtotal)...see attached. Can you let me know how you did this (it's a setting or how you built the Pivot Table or something else)?

Thank you very much, in advance, and thank you for showing me a novel way to look at something that I have seen every day for many years .

Yury