I realize there is currently no smartsheet function equivalent to the OFFSET function in Excel. (But it would be REALLY nice if there was one!) Until that particular enhancement request is finally achieved...I need help finding a workaround.
My situation: I have a sheet of data where the columns contain individual employee hours data and the rows contain specific job names by week. Below is a screenshot of a simplified example of my sheet. (The "TOTAL" rows are SUM(CHILDREN()) formulas. The "Total Hours" column is a simple SUM formula of the three "Employee" columns.)
My dilemma: I need to create a report that lists all the unique job names and totals up each employee's hours on each job for the entire year. (This will actually be a separate sheet from my main data sheet, NOT a smartsheet report since smartsheet reports can't have formulas...which is another enhancement request dream of mine.) The screenshot below shows an example of what this report would look like.
So. How can I (on a separate sheet) have a formula that SUMIF's the hours related to a specific job name AND only looks at a single employee at a time? I need a report that shows me the total hours each employee had on each job throughout the year.
Any insight provided will be appreciated!
