Row Report: Only show if all sheets are present
I have a row report that grabs data from two sheets. Everything gets Grouped by a primary identifier. Because the two sheets are generated at different times (think a multi-stage form), I'd prefer to only show the data if both sheets are present. However, I cannot figure out how to use filters to do this. Any advice?
Answers
-
Hey Katheride,
I would suggest having a checkbox with a formula on one of the sheets with something that shows that the data appears on both sheets.
That way you can just add this sheet to the row report and then filter all data sets that are checked.
-
@katheride Unfortunately @Brucey's solution (or something very similar to it) is going to be your only option unless you just wait to create the report until both sheets are present. There is no way to use filters to do what you are wanting.
-
I've been meaning to include my own solution for this. Brucey's solution does not work fully. Let's assume that we have two sheets, Sheet1 and Sheet2. Sheet1 has its dated populated by a form and then produces a form that adds rows to Sheet2. In Sheet1, there is a column UID, which is a unique ID. UID is passed via query parameters in the form so that it will be used in Sheet2 as well. The report will group rows based on the common UID. The goal is to only show rows from both Sheet1 and Sheet2 if the UID exists in both. A single helper variable is not sufficient. What you need to do is create a helper variable in each sheet that mutually looks for the UID in the other. Then do filters specific to a sheet and the existence confirmation on the UID in the other sheet.
I'll post my actual solution later as I'm about to go into my Wednesday meeting gauntlet.
