OFFSET function equivalent?

Amy Foeller

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!

Paul Newcome

You may be able to use a report if you group by Job Name then use the report summaries to "sum" each of the employee columns and the total column.

Sheet sample:

Report sample:

Amy Foeller

@Paul Newcome thank you for your suggestion, but I can't really use a report for this. We have over 500 unique jobs and around 40 different employees in a given year, so this report would be cumbersome and not very easy to view. Also, I want to be able to copy/paste the data from here elsewhere, and I can't do that with report summaries.

I'm guessing I'll have to do my usual workaround of doing these calculations in Excel and then use Data Shuttle to import the raw data into my sheet.

Thanks!