Is this scenario possible?

HuiXian

Im trying to find out if the possible scenario is possible in smartsheet:

Im trying to create a time based resource model with input from a questionnaire.

I have the resource requirements in 1 data sheet (ie what resource does it take to complete a list of activities for an item) - Sheet 1 below

i have a 2nd sheet that will tell me how many quarters it takes to complete the activity - Sheet 2 below

im trying to find out if i can combine both sheets to give a scenario where i can map out resource requirement for each quarter?

i tried to use the question number as my unique identified but as it changes with each row, im unable to do a column formula

Sheet 1:

Sheet 2:

Genevieve P.

Hi @HuiXian

I can't quite tell how your data is being mapped in your sheet 1, so please forgive me if I've misunderstood your question!

It sounds like potentially you could use a formula on your second sheet that counts how many quarters are associated with the value. Since you have half-quarters you would want each cell to equal 2, and a half-cell to equal 1. (e.g. AD1T1 would equal 5).

=(COUNTIF([Q1]@row:[Q6]@row, <>"") * 2) - COUNTIF([Q1]@row:[Q6]@row, CONTAINS("0.5", @cell))

Then you could get a SUM of AD1T1 data from sheet 1 and divide it by that number to see how much is needed per quarter:

=SUMIFS({Column to Sum}, {Column with unique Question Number}, [Question Number]@row) / [Quarters Formula]@row

Once you know what the resource is broken down by 0.5 of a quarter, you can use SUMIFS in the top row for your totals per quarter, like so:

=(SUMIFS([SUM formula]:[SUM formula], [Q1]:[Q1], <>"", [Q1]:[Q1], NOT(CONTAINS("0.5", @cell))) * 2) + SUMIFS([SUM formula]:[SUM formula], [Q1]:[Q1], CONTAINS("0.5", @cell))

This applies 2x to the SUM value that we found for any cell without 0.5 in the column, and only 1x the value for 0.5 cells.

Let me know if this makes sense and provides you with the type of summary you're looking for!

Cheers,

Genevieve

HuiXian

Hi Genevieve

You gave me an interesting perspective - let me explore this!

For Sheet 1 - it is being populated via a Form. Form provides a response and response looks up the value from another sheet.

Thank you!

Cheers

HuiXian