simple bill of quantities sheet

[hansmoleman]

hi all,

i'm trying to make this spreadsheet but im having trouble figuring out a way to put a rate buildup in the rates column of my speadsheet. see link below to see what i mean. could this be achieved with excel or do i have to use access?

also, how do i go about making the primary key?

http://www.flickr.com/photos/56891426@N07/5249968993/

 

[Rastaman]

Do you want the value in the rate column to reflect rate for the ID selected in column D? If so, assuming your data is on sheet2 with column A being the ID and column C being the rate, use this formula in cell H2 and drag down:

=IFERROR(VLOOKUP(D2,Sheet2!A:C,3,0),"")

This will return a blank if nothing or an invalid ID is entered into the ID cell in column D.

Rick

 

[hansmoleman]

Do you want the value in the rate column to reflect rate for the ID selected in column D? If so, assuming your data is on sheet2 with column A being the ID and column C being the rate, use this formula in cell H2 and drag down:

=IFERROR(VLOOKUP(D2,Sheet2!A:C,3,0),"")

This will return a blank if nothing or an invalid ID is entered into the ID cell in column D.

Rick

thanks for the reply rick,

but no.
that items 1,2,3 coming from the rate cell is just the rate buildup.
i just want the total rate in in the rate column to reflect the ID. i dont know how to drag down and have the total rate in the rate column and the working out in the second sheet. (whilst having the calc in sheet 2 linked to the rate in sheet 1)

 

[hansmoleman]

how do i make the total rate link with the rate cell in the first sheet so that i can do calcs in the other sheet and then just drag down the rates in the rate column in the first sheet?

http://www.flickr.com/photos/56891426@N07/5252482705/

know what i mean?

 

[Rastaman]

Maybe this, which sums up all the occurrences of the rate for an item number. However, you would have to remove the item number from your Total row in sheet 1, or eliminate the Total row. (entered into cell D2 and dragged down)

=SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$D2)

If you must keep the total row and the Item number on sheet 1, here's an alternative formula.

=SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$D2,Sheet1!$B:$B,"<>Total")

Rick