Multiple Checkbox's for sheet summary into a dashboard.

Christian Reagan

Good afternoon, I am looking to see if this is possible. I am looking to have a dashboard automatically populate with a new row when another is "complete". Which I am able to accomplish with automation tasks. The issue is building the dashboard that has multiple columns of checkbox's. Please see the photos below for an example.

So this is how my sheet is utilizing checkbox's looks currently.

I have been tasked with creating a dashboard that once the checkbox is checked for "Complete" it moves to a archived sheet. Which is working as intended.

What is not, is the dashboard that they are wanting for this sheet. See below.

So this is a section of the dashboard. Currently we have been using the metric widget and referencing the cells. But as you can imagine when they move from the sheet they were referencing it shows "Missing Data". Now for all of the other non-checkbox fields that were causing this issue, we have been using the "Sheet Summery" fields and indexing them. this way when one falls off the next row populates accordingly.

Finally on to my issue, I have been unable to find a combined formula that will simply show all of the checkbox columns in a group like it shows above. Or simply a Yes or No in reference to there respective column. I was hoping that if this is possible if anyone could enlighten me.

Obviously, I know this post may be a bit convoluted. So if more detail is needed please feel free to ask.

Genevieve P.

Hi @Christian Reagan

How many rows will be displayed on the Dashboard at one time?

Something we could do would be to set up a separate sheet with cross-sheet formulas that automatically populate the first X number of rows with the same content that's in your source sheet.

That way you can use your Metric widgets in the exact same way, but since the rows will be the same (meaning none of them move) then your widgets will update as the formulas update.

To bring back this data, you can use an INDEX function to bring back the cell in a specific column based on row number in that sheet.

For example, row one:

=INDEX({Staging Needed Column}, 1)

For example, row two:

=INDEX({Staging Needed Column}, 2)

You could set up a "Row Number" column in this sheet to simply add numbers down a column and reference that instead:

=INDEX({Staging Needed}, [Row Number]@row)

I would personally have one of the columns set up to pull the RTP # as well, so that you can use that as the Top value in your Metric Widget instead of adding a manual title. This will allow content to rotate through your Dashboard. Does that make sense?

Cheers,

Genevieve