Pulling data from a sheet


Good Morning,

I have a report that I do monthly that records the data from the following sheet. At the end of the month I usually filter columns and manually count errors for each responsible party and move them to a another sheet that records each persons errors for the whole year. I record data for about 30 individuals so the sheet below and get full. I am trying to cut down on time if possible that I do my monthly reports.

What I want to know is there any way to pull data from this sheet ^ using some type of formula or indexing without filtering and manually counting each month. On another report I use, I use the formula COLLECT to pull how many jobs, hours, and whether each designer stays within budget from a sheet of 200 rows but it paste the info in a single cell. I am looking to extract data from the sheet listed above but in this form: Responsible Party, Error Category, Brief description on another sheet. I hope this makes sense, but please let me know if you have any questions.


Best Answer


  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Austin Bell

    That's quite doable.

    To extract data for the second sheet, I would suggest to do a report, that's gonna be way easier, and Reports are designed for this.

    For the record of errors, I would suggest this formula on your second sheet:

    =COUNTIFS({Date Range 1st Sheet}, MONTH(@cell)= MONTH(TODAY()), {Person's name Range 1st Sheet}, [Person's Name]@row)

    This will automatically calculate each time the name of the person appear on the first sheet within a MONTH.

    You can also adapt the formula replacing MONTH(TODAY()) by the number of the month you want to record (if you have a column for each month for example.

    Last, if you design both Sheets with the same column names and add your record sheet to the report, Smartsheet will display the columns with the same names as 1 column. (That is case sensitive, but works nicely).

    Hope it helps!

  • Austin Bell

    This is working and thank you both!

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Great to hear Austin.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!