vaidmohammed
Member
Hi,
Attached is my budget allocation sheet. I am facing a problem in deriving a formula for getting the utilized amount.
There are 4 worksheets :
Costs --> Here my Associate's list and their designations are mentioned, under columns A to C. As well as designation wise per day cost is also mentioned under columns E to Q.
Budget_Master --> Here my WON (work order number) is mentioned along with each WON no. budget. Col. B is having WON No. and Col. F is having a WON budget.
Allocations --> Here I am going to provide allocations to my team under WONs. (Here, under 1 WON multiple associates can be allocated with from and to dates.) Total no. of allocation days are mentioned under Col. G.
The last sheet is Balance, where I need your help in deriving the Utilized amount under Col. D.
Under the Utilized column I want a formula that can derive an amount considering the following :
1. It should be the utilization of respective WON No. (mentioned under col. B) by considering
a. the associates allocated
b. their no. of days of allocation from sheet Allocation.
c. multiplying with the cost of respective designation, mentioned in Costs sheet Under column E2 : Q3
The formula should be considering :
1. Excel should check the name (Col. C) under sheet Allocations and check their designation (Col. D).
2. Then, excel should check the cost of that designation in the Costs worksheet from col. E2 to Q3 and find the cost (Row 3) of that particular designation cost.
3. Then, excel should multiply the identified cost with No. of days (Worksheet Allocation --> Col. G)
4. Then, excel should sum these all values for each respective WON nos. and show that summed figure under column D of the Balance worksheet.
I know it is very complex. But please advise.
Attached is my budget allocation sheet. I am facing a problem in deriving a formula for getting the utilized amount.
There are 4 worksheets :
Costs --> Here my Associate's list and their designations are mentioned, under columns A to C. As well as designation wise per day cost is also mentioned under columns E to Q.
Budget_Master --> Here my WON (work order number) is mentioned along with each WON no. budget. Col. B is having WON No. and Col. F is having a WON budget.
Allocations --> Here I am going to provide allocations to my team under WONs. (Here, under 1 WON multiple associates can be allocated with from and to dates.) Total no. of allocation days are mentioned under Col. G.
The last sheet is Balance, where I need your help in deriving the Utilized amount under Col. D.
Under the Utilized column I want a formula that can derive an amount considering the following :
1. It should be the utilization of respective WON No. (mentioned under col. B) by considering
a. the associates allocated
b. their no. of days of allocation from sheet Allocation.
c. multiplying with the cost of respective designation, mentioned in Costs sheet Under column E2 : Q3
The formula should be considering :
1. Excel should check the name (Col. C) under sheet Allocations and check their designation (Col. D).
2. Then, excel should check the cost of that designation in the Costs worksheet from col. E2 to Q3 and find the cost (Row 3) of that particular designation cost.
3. Then, excel should multiply the identified cost with No. of days (Worksheet Allocation --> Col. G)
4. Then, excel should sum these all values for each respective WON nos. and show that summed figure under column D of the Balance worksheet.
I know it is very complex. But please advise.
