Can you filter on Sums in a summary report

KHannon

I have a time entry report in smartsheet and I only want the report to show employees who have less than 40 hours for a week. I do have the Pivot app at my disposal if this will get me the result needed. The only option I am seeing in filters is Grand total sum - I need subtotals by the employee.

The report has 3 filters - Hours are not blank - Date range - Approver is not blank

The report has 2 groups - first by Approver and then by Employee name.

The report has one Summary - Total hours

Debbie Sawyer

Hi

The grouping and summarising in reports is a new feature and I don't believe you can filter on the Group Sums currently. (you never know this might be something you can do in the future?)

Have you considered putting the sums in a grid sheet using cross sheet referencing to work out the hours per employee and using this stats sheet to report on who has hours left etc.

I believe the conditions you are writing into your reports, you could reproduce in formulae instead?

Kind regards

Debbie

KHannon

Do you know if there is a max number of sheets that can be used in a cross sheet reference? Currently, we have over 200 active program timesheets that multiple employees log daily hours?

What you are suggesting is to create a sheet that has all employees listed - then using cross sheet referencing to pull in their weekly accumulative hours - from there, create a report that would show any employee who did not have 40 hours logged for that week. Am I close or way off base?

What type of formula would you suggest that would use a date range?

Appreciate your help.

Debbie Sawyer

Yes, I was suggesting that but with 200 sheets, that might be a bit tricky to achieve!

May I ask why there are so many timesheets in your solution?

Most clients that I work with have 1 timesheet that all employees use but they select the active program from a drop down list to log the time against? If all hours were in one sheet then the cross sheet referencing thing would be a lot easier!

There are dropdown list managers that could help collect the active names of the programs in order to populate the timesheet dropdown list. See here: https://smarterbusinessprocesses.com/smartercontrols4smartsheet/

The question you put in about formulas with date ranges, there are a number you can use depending on what range you want. If it is always the current week, you could use the =WEEKNUMBER(date to compare)=WEEKNUMBER(TODAY()). For example this formula sets a column value to 1 if the date in the column I'm comparing is the current week:

=IF(WEEKNUMBER(date@row) = WEEKNUMBER(TODAY()), 1, 0)

Or you could use formulas that work on Today(-30) if you always want the last 30 days, or you could compare the Month number of the date you are comparing to the month number of a current date etc.

I hope this helps a little. Sorry it isn't a full response to help with 200 sheets!

Kind regards

Debbie

KHannon

We use a timesheet report that lists each team member's active programs to select from ( no dropdowns). The report looks at the current user to determine what they see.

I am going to play around with some of these date formulas; thanks for the suggestion.