Creating a Dashboard Using "Sheet"

plugo789_tfin

Hello all,

I'm new to using Smartsheet's and i'm hoping someone can give me advice on how to successfully create a dashboard via "Sheet". The trouble i'm having is finding the correct formula. I want the Dashboard to display the number of invoices submitted by submission branches, for the 1st quarter 2024.

SoS | Dan Palenchar

Hello @plugo789_tfin,

To do this you will need to first get the specific data you want out of your Sheet with some formulas. Then, you will need to display that data on a Dashboard with the widget of your choice (a chart, a report, metrics).

Without seeing your Sheet I can't say exactly what to do, but I'm guessing you have a list of invoices with columns for the Submission Branch and Date.

You should set up a second Sheet for metrics and list all of the Submission Branches in it. To count the total number of invoices submitted by branch you would make a Cross Sheet formula to reference your existing Sheet of invoices using the formula:

=COUNTIF({Submission Branch Column on Invoice Sheet}, [Submission Branch]@row)

Note that the bolded portion above is a Cross Sheet reference where you select the entire column in the Sheet reference window by selecting the column name.

To isolate only the invoices that fall within a certain quarter (i.e., 1st quarter of 2024) you will need to additionally check that the Date field falls within a particular year and between the start/ end date of the quarter you are interested in. I've solved some similar questions like this before, see this published Sheet which is also shown below.

https://app.smartsheet.com/b/publish?EQBCT=cbbaf5decbc84e1c9178487e8dc7f0a6

So ultimately you will need a COUNTIFS() formula that checks both the Submission Branch as well as the Date, perhaps by referencing something like Q-Yr output in the Sheet above.

Hope this helps!