How do I get my report to pull in ALL "modified by" transactions across multiple sheets?

ECD Auto Design

I currently have a report where i am checking to see when an individual checks a box between a given time frame. I have set up parameters to look for a specefic user and i have selected all the sheets i want to pull the data for but it still seems to be missing transactions when i spot check across the sheets they are working on. could use some help trying to figure out how to get the report to pull ALL the data across the sheets im asking it to look at.

Here is an example of me asking the report to look at this individual during this time frame across 512 sheets to see what boxes they have checked

In this second picture I have the sort for newest to oldest transactions and it doesn't seem to be pulling in the data from the third picture

Any help would be appreciated. Thank you for your time🙂

Adam Murphy

If I follow you correctly you are trying to report on when a checkbox is checked within a date range using the modified field, is that correct? That modified field will keep changing so I don't think this can be used as a reliable record of when a change was made. If it were me I would add a helper column of type DATE and an automation that triggers when the checkbox is checked and records a date in the helper column, then simply report against that helper column date which should be very reliable at that point. Hope this helps.

ECD Auto Design

@Adam Murphy Thank you for this suggestion. with the automation trigger does that still allow me to see who checked the box and at what time? So let say i set the automation to trigger when the cell is checked and then record a date will the recorded date still tell me "WHO" activated the triggered date? the biggest thing i'm trying to record is how many boxes they check off in a given week

Brian_Richardson

You are probably not seeing all the data you expect, because you're trying to compare the Activity Log to the Modified / Modified By, which are two different things. The Activity Log is showing you each change and who made it. Modified is showing you the date of the latest change and who made it.

To get the full list of changes, including who changed it and when, follow these setup steps:

1. Create a blank sheet
2. Ensure your first sheet (where people are checking the checkbox) has the Modified and Modified Date system columns in it.
3. On the first sheet, create an automation. Trigger should be when rows are added/changed, and the checkbox is checked. You don't need a condition. The action is Copy Row, pointing to the new blank sheet that you just created.

Now whenever the checkbox is checked, the entire row will copy to the second sheet. It will include all the columns from the row, including Modified and Modified Date. You can then setup your report to look at the second sheet instead of the first, and you'll have all the data to sift and sort.

You can further enhance your solution by Pivoting the "log sheet" if you have the Pivot app.

The drawback to this method is that your second sheet might get full pretty fast. You'll need to periodically clear it out, which you can do by deleting rows. You may want to snapshot save the "log sheet" first before deleting it. If you save first, update your report to look at the archived copy as well as the cleaned out sheet, and you'll still have all your historical data.