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
-
If I understand the requirements correctly, I think you'll want to look into the Copy Row or Move Row functions under the Automation tab. You can set those to whatever rules you want to specify and it will either copy or move the rows to your Error calculation sheet once the trigger has been set.
Answers
-
If I understand the requirements correctly, I think you'll want to look into the Copy Row or Move Row functions under the Automation tab. You can set those to whatever rules you want to specify and it will either copy or move the rows to your Error calculation sheet once the trigger has been set.
-
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!
-
This is working and thank you both!
-
Great to hear Austin.
Help Article Resources
Categories
Check out the Formula Handbook template!
