Setting up resourcing sheet

Allie Forbes

I'm using smartsheet to manage assay requests that my team receives. On a separate sheet, I have calculated the amount of time it takes to complete each type of assay, which varies by sample number. I would like to set up another sheet to display the amount of time each assay operator is putting towards running each type of assay.

For example, if Joe receives a request to run Assay 1 with 55 samples, I want the amount of time it takes to run 55 samples for Assay 1 displayed in the resourcing sheet next to Joe's name. Ideally, I would like to tally the total hours that each operator puts towards running all assays per quarter. Joe will run different amounts of assays with different amounts of samples each quarter, and I would like a breakdown of the time Joe puts towards running assays each quarter.

Does anyone have any thoughts on how I could accomplish this?

This is a screenshot of the assay time calculations per sample (the thaw/prep time is not dependent on sample number)

Genevieve P.

Hi Allie,

We can use a cross-sheet INDEX(MATCH to return the value in the "Acquisition and Analysis time PER SAMPLE" column to the other sheet, and multiply it by the number of samples, to return a total time for that Assay.

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value looking to match in the other sheet}))

Try this:

=INDEX({Acquisition and Analysis time PER SAMPLE}, MATCH(Assay@row, {Assay column on other sheet})) * [Number of Samples]@row

This presumes you have a column for the Assay and Number of Samples in your current sheet, along with Joe's name. If I've misunderstood this, it would be good to see your destination sheet set up in another screen capture (but please block out any sensitive data).

Cheers!

Genevieve

Genevieve P.

Hi Allie,

We can use a cross-sheet INDEX(MATCH to return the value in the "Acquisition and Analysis time PER SAMPLE" column to the other sheet, and multiply it by the number of samples, to return a total time for that Assay.

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value looking to match in the other sheet}))

Try this:

=INDEX({Acquisition and Analysis time PER SAMPLE}, MATCH(Assay@row, {Assay column on other sheet})) * [Number of Samples]@row

This presumes you have a column for the Assay and Number of Samples in your current sheet, along with Joe's name. If I've misunderstood this, it would be good to see your destination sheet set up in another screen capture (but please block out any sensitive data).

Cheers!

Genevieve