# Thread: Formula Problem (Excel 2000)

1. ## Formula Problem (Excel 2000)

Hi everybody

I have developed a workbook with the following work sheets in it:
Resource Prices
Here the unit prices are listed, example price per bag, price per cubic meter, etc
RatesL1
Here resource prices are used to build up rates.
RatesL2
Here rates from RatesL1 is used to build up the rates that are used in the BoQ
Resource Detail
Here is the collection of the total resource cost.
BoQ(Bill of Quantities)
Here is a bill of Quantities priced out with the rates as build up in RatesL2

My problem is a formula that can be used to extract the resources used in a single BoQ item. For example if I fill in the item number on the Resource Price work sheet in cell G2 I want the cost of the individual resources listed in column G against each resource in column C (similar to the Resource Detail sheet)

Any help or ideas will be appreciated.

Regards

2. ## Re: Formula Problem (Excel 2000)

Perhaps it is just the Monday morning haze in my brain, but i do not understand what values your want in G3:G14 when G2 is (eg) 1 and the logic for the values.

Could you elaborate?

Steve

3. ## Re: Formula Problem (Excel 2000)

My apologies for not explaining better.

Please compare the total of Column F on the ResPrice and the total of column H on the BoQ sheets, they match because both totals contains the full resources used.

Now on the BoG sheet, delete in column F, F9:F33 leaving only the quantity in F7. Now compare the total of Column F on the ResPrice and the total of column H on the BoQ sheets, they match again, but now column F on the ResPrice sheet only contain the totals for item no 1(ref B7) on the BoQ sheet. Obviously item 1 is only used for an example but it must work for any item that appear on the BoQ sheet.

What I want is not to delete the information on the BoQ sheet to get this answer, I want to type in the item nr in G2 on the ResPrice sheet and then the same totals as is column F now (after deleting in column F, F9:F33 leaving only the quantity in F7) must appear in column G on the ResPrice sheet.

Thank you.

4. ## Re: Formula Problem (Excel 2000)

How about this? I added an intermediate column in RatesL1 and RatesL2. I named one crit3a and for the other, expanded the ResSum database to include the new column(partial) and this is what I use to do the sum

Steve

5. ## Re: Formula Problem (Excel 2000)

Steve

Absolutely wonderful, you make it look so easy.

Thank you very much, I have been cracking my brain for quite some time now but would not have come up with your solution.

It is working perfectly.

Thank you very much again

Regards

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•