How do I reference a Report Summary Field into a formula

John Erickson

I created a Sheet Summary Report for one of my Sheets.

The Sheet Summary Report is grouping by JOB NAME and giving me MAX values for certain criteria

I have a metric that is calculating a RATE and I do not want the MAX value of this rate in my sheet I only want the rate for the row in my sheet that has the 2 MAX values.

What I need to do is be able to take 2 MAX values that are being displayed on my Sheet Summary Report and divide one over the other. But I can not access the data in these cells on the Sheet Summary Report, nor from a new sheet.

Specifically I have a QTY and HOURS and I am trying to calculate the QTY/HR

In my case I really am only interested in the most recent row in my sheet.

How my sheet is generated is by updating QTY and Hours for those respective Jobs on their own sheets. I have a master sheet that is generated using automation, so every time I update the QTY and HOURS on one of my job sheets a new row is populated on my master sheet.

Once that new row is made there is another automation that adds the current date to that row so that on my master sheet I can see every time the QTY and HOURS were updated for each job, it shows up as a new row and the dates are recorded. I can sort the sheet by job then date and thus am able to find the latest recording but what I wanted to do was create a summary of the master sheet that only shows the latest recording per job

In order to do this I made a Sheet Summary Report that groups by JOB NAME, then in order to find the latest data I actually couldn't use the date column because there was no way to say "Give me the data only for the latest date for a unique job name" so to work around this I have it summarize the MAX HOURS and MAX QTY. The reason MAX works is because my hours and QTY are always increasing and never decreasing (Unless there was an error in inputting in which case I can manually change the master sheet).

This has been useful in summarizing all my jobs and seeing my current HOURS and QTY, but I can not use the MAX of the QTY/HOURS because this can go up or down depending on my productivity at the time. really what I need is the MAX QTY/ MAX HOURS and all I would need to do is just have a simple division formula in one of my cells but I am unable to type anything in any cell on a Sheet Summary Report.

What do I do?

Humashankar

Hi - I hope that you are Doing good !!

Solid problem statement to solve and a detailed write up, i would call it out

With respect to MAX values from your Sheet Summary Report into a formula in Smartsheet, 

Try using the = SUMMARY function

Make the calculation with the QTY/HR ratio

=SUMMARY("Sheet Summary Report Name", "MAX", [Column ID]@row)

Hope this helps

Thank you very much and have a fantastic day!

Warm regards