Project Resource Calculation with flexiblity(drop-down)

[dheeraj1688]

Hi,

I'm trying to calculate resource required to complete a project.I'm facing problem because i provide so much drop-down for changing preference, now i'm ran out of excel formula limit around 8100 characters limit.

3 project(tender) : Mars, Jupiter,Pluto...(In sheet-storing no of tickets raise in individual project)

To achieve above 3 or any single we require 4 different product.( In sheet- no. of hours of different activities)
1.Monday
2.Tuesday
3.wednesday
4. Thursday

In each product we do n no. activities.
for ex- To complete MARS Project :
4 activities from monday,2 from tues, 5 from wednsday (products)
for ex- To complete Jupiter Project :
5 activities from monday,4 from tues,9 from wednsday and 14 from thurday (products)

i'm using 3 sheets here:
1. Task sheet : Which having columns :
a. No. of tickets - total no. of tickets of all projects.
b. Roles : no. of hours required from that assigned role person.
c. execution hours : mulitplication of above two(a *b)

2. Project sheet:
a. Activities : List of all activities from different products to complete the project.
b. product list :

3. Anlysis sheet:
a. Product Wise Resource Requirement (Calculate using if condition but ran out of excel formula character limit.)
b. Project Wise Resource Requirement( Not able to calucalte)
Any one can please suggest a way or different approach to complete this. Or do i need to freeze all drop-down list.
I higlighted all drop-down with red color cell.

 

[Nebu]

Hi:

I am afraid I understood you fully I have constructed a sum if formula in the field you had pointed as automated. Find the attached.

Thanks

 

[dheeraj1688]

Hi:

I am afraid I understood you fully I have constructed a sum if formula in the field you had pointed as automated. Find the attached.

Thanks

Hi Nebu thanks for reply ..
But i think u little missunderstood it . I actually want to calculate in analysis sheet "2.Project Wise Resource Requirement".

because using "If formula , i was exhausted with excel character limit".Is there any other way to calculate this.

 

[dheeraj1688]

Hi..update row name ,by mistake entered wrong details.



Please use below attach file .

 

[Nebu]

Hi:

I am not sure I understood you correctly, which all cells are you looking for formulas on your analysis tab, there is nothing mentioned on that tab other than some bubbles explaining how you calculated certain values.

• From where are you getting 10, 5, 1 & 4?
• On what basis you want to map the values on to the analysis tab?

Thanks

 

[dheeraj1688]

Hi nebu
Thanks for bearing me.
Attaching doc. for ref. hope this time it will help.

 

[Nebu]

Hi:

I could not make out fully what you wanted. I have put a formula as per my understanding. Let me know with questions if any.

Note: I would recommend you to rearrange the data in a more user friendly manner so that the calculations will not be this confusing.

Thanks