Dashboard Metric sheet formulas

RGarrels

I am new to Smartsheet and have been tasked to make a Dashboard that feeds from a large spreadsheet.

I have created a metrics sheet and for the most part, it works well for the dashboard, but I haven't been able to solve the following issues;

1) How do I make the metrics sheet update when a new entry is made in the main spreadsheet- ie: if a manager adds a new 4th "modality" that isn't currently on the metrics sheet list of 3. Can this be automated?

2) Is there a formula that can "count" spots that are blank, or should a drop down option of NA or TBD be added to the main spreadsheet?

3) Is there a formula that could capture the information if the cells on the main sheet are a multi-dropdown, and more then one option has been selected?

Thank you so much!!

Genevieve P.

Hi @RGarrels

1) This will depend on how you are grabbing the data and what type of widget you're displaying on the Dashboard. For example, if you are using a cross-sheet COUNTIFS formula, you would need to manually include this new "modality" on your Metrics sheet and drag-fill down the formula to reference a new item.

One way to automatically include values would be to create a Report looking at the sheet instead of using formulas. Then GROUP the Report by the Modality, and use the SUMMARY function to automatically create numbers (such as a COUNT of each Modality). Then as new values are added to the source sheet, the Report will automatically create a new grouping and tally up the numbers for you.

You can put a Grouped Report into a Dashboard using the Report Widget, then choose to Collapse Grouped Rows. (Note: our team is currently working on the ability to use these grouped rows in a Chart Widget. You can sign up to the Release Notes page if you'd like to be notified when this feature is released!)

2) Yes, you can COUNTIF the cells are blank, or ""

Ex:

=COUNTIF({Column 1}, "")

Keep in mind that this will count the 10 fully blank rows at the bottom of your sheet as well. You can either use -10 at the end of your formula to subtract these rows, or add in a criteria to your formula so it only looks for blank cells in Column 1 if Column two is not blank. Does that make sense?

=COUNTIFS({Column 1}, "", {Column 2}, <> "")

3) Yes! The HAS Function will search for a specific value selected in a multi-dropdown list. For example, if you have "Oranges", "Apples", and "Pears" as three possible selections, you can COUNT to see how many cells have "Apples" selected (with or without other selections):

=COUNTIF({Multiselect}, HAS(@cell, "Apples"))

If you're looking to count how many options have been selected in one individual cell, you can use the COUNTM function:

=COUNTM([Multi Select]@row)

Add this to a new column in your source sheet, then apply this as a column formula. This will count how many options are selected in each row. Then in your cross-sheet Metrics formulas you can use the helper column to see if the Count is greater-than-1:

=COUNTIF({Helper Formula Column}, @cell > 1)

If you have any additional questions, it would be helpful to see a screen capture of your source sheet and of your metric sheet, but please block out any sensitive data.

Cheers!

Genevieve