Pulling cell data based on date and name

Hello,

Could someone help me with a bit of a pickle I can't get around?

I have a sheet with multiple dates and names and their daily progress. The dates are pre-populated for the entire month with everyone's names assigned for each day. I've been trying to figure out how I can extract the status percentage for a specific individual and today's date so that I can use that cell as a metric on the dashboard, displaying the progress for the current day. Any thoughts?

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    I do something very similar. I use a metrics sheet to pull the data from a larger sheet, then reference that metrics sheet for the dashboard display. The example below uses SUMIFS, but you could use AVG to obtain the average status if your status is a percentage or numeric value.

    In my example, I want to show the total value of all Bills of Lading in Closed status for each warehouse employee. In my metrics sheet I have a Name column and a Data column. Obviously the employee name goes into the Name column, and in the data column I use SUMIFS and reference the grid sheet (called "BOL Active Tracking") as follows:

    =SUMIFS({BOL Active Tracking Value}, {BOL Active Tracking Status}, "Closed", {BOL Active Tracking Assigned To}, Name@row)

    In plain English: Add up the values in rows in "BOL Active Tracking" sheet with Closed in the Status column, where the Assigned To person matches the Name column for this row.

    When writing your formula, use the prompt to "Reference another sheet" to select the appropriate column range in the project sheet. The system will allow you to select the sheet, the column, and to name the range something meaningful to you. In my case I just use the Sheet name followed by the Column name, ex {BOL Active Tracking Value}. The system adds the {} brackets around the reference range.

    Result:

    Name Data

    JOHN SMITH $189,560.00

    JANE DOE $157,495.08

    JACK SPRAT $24,128.00

    etc.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Thanks Jeff, this solution for me on a different scenario but based on the same use case.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    Fantastic! 4 years later. (This is why I always try to explain my answers in detail!)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!