How to manage rolling content updates

22Wordsmith

I have a sheet that is accessed by many reporters.
Each of the 500+ row is cleared with a checkbox to indicate it can be sent through to a Power BI report. If the box is unchecked, that content is removed from the next report refresh.

Can you suggest the best way to set this up so that the report continues to show information until new content is entered and cleared?

I'm thinking it might need a 2-sheet solution so that users are forced to clear each line but I am not sure how to get the new content into the sheet that is referenced by the report. I'm leaning towards a workflow solution but can I make one to copy and replace content based on a Reference term? I want to avoid a sheet that has multiple entries for the same reference term.

Brian_Richardson

Sorry, I'm not really clear on what you're trying to do here.

How are you getting data into your sheet, why are there checkboxes, and how are the checkboxes getting set and cleared today, and how would you like it to work in best case scenario?

22Wordsmith

Hi Brian - The checkboxes are required because we are getting information from many sources. Each source marks the checkbox at their executive level to confirm that the content is cleared for release into a report.

I want it set up so that if Barney writes in June on Row 1 that Bam Bam is learning to walk and doesn't update that again until October, the information still appears in reports for July-September.

Meanwhile, Fred is providing monthly cleared updates on Pebbles against Row 2 and those appear in the relevant monthly report, changing each time.

Brian_Richardson

So, to summarize the process, is this right?

1. Barney makes an update to the row
2. You would like that row NOT to show up in any reports until Fred "clears" it for reporting
3. Once cleared for reporting, it remains in reports until Barney makes another update, at which point it should no longer show up until cleared again.

Right?

If that looks right, then you can do this with a couple of automations.

Setup a date column called something like "Last Change Date for Validation". Then, setup an automation that captures the date into this column when an applicable change is made to the row. You don't want just any change, because otherwise it will trigger every time the checkboxes change. Instead, you want this automation to trigger when the description, or attachments, or whatever it is that you have people updating changes.

Now setup an automation for each approver's checkbox that triggers when the "Last Change Date for Validation" changes to some date (not blank). When triggered, the automation clears the checkbox.

That then should operate as described above. When Barney makes a change, your automations will clear all the approval checkboxes. Then your approvers can check the boxes.

For your reporting, simply filter for the items with checked checkboxes. If you are showing reports that only include changes made in a given month, use your "Last Change Date for Validation" column dates in the filters. If you need to filter for just a month then you may want one more column in your sheet that picks up the month number using a formula =MONTH([Last Change Date for Validation]@row). Then in the report for June, filter for the value 6 in that column.

22Wordsmith

Hi Brian - step 2 in your summary is not quite right.

Barney and Fred are reporting independently of each other. So, Barney will clear his material and Fred will clear his own entries.

Barney makes update on 01.01 and clears it.

Fred makes an update on 03.01 and clears it. These should appear in the report run on 05.01

Barney makes no further changes

Fred makes an update on 09.01. The report run on 10.01 should show Barney's comment from 01.01 and Fred's from 09.01.

I think the validation factor you mentioned should manage it but can you confirm, please?

Brian_Richardson

sounds like it should be fine. If nothing is unchecking those checkboxes because no change was made, then it will show up in your report if you’re filtering for the checkbox.