Is this scenario possible?
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:
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!