Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Hope this make sense now, if not please ask again.

    Thank you.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •