Creating a Dashboard Using "Sheet"

Options

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.

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    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.

    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!

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!