Three Sheet Data Problem

[tyba]

I have information on three different sheets.

On one sheet I have a list of products, identified by a product number. This sheet contains a products price.

On a second sheet I have purchase orders, also identified by a bill number, this sheet indicates what products have been ordered and what amount of the product was ordered.

On a third sheet I have billing numbers and an empty column where the total cost of the bill will go.

I need to multiply the number of products ordered (product number and amount ordered on sheet 2) by the cost of the product (product number and cost on sheet 1). These amounts then need to be summed together if they are a part of the same bill.

A	B
drug_id	whole_sale_price
1	1.95
2	2.25

A	B	C
bill_no	drug_id	units_purchased
1	1	1000
1	2	500

A	B
bill_no	cost
1

[zbor]

Can you upload example workbook?

[tyba]

I'm figuring out how to do that now... just a second

[zbor]

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[tyba]

I don't think I uploaded the way you intended me to, but the sheets are named drugs, purchases_amount, and purchase_date

[zbor]

OK, in what sheet now you want to sum what?

[tyba]

In Purchase Date I need the total cost of the bill.

All of the purchases for the bill, the drugs bought and the amount bought, are found in the "Purchase_Amount" tab

The cost of each unit of a drug is found on the "Drugs" tab, under the "whole_sale_unit_price" column

[tyba]

My data tables aren't complete, I'm still playing with them. I'm looking to find the total for "bill_no" 1 in the "purchase_date" worksheet.

[zbor]

Here. I've added helper column (yellow) named total

[tyba]

That's great, thank you so much!

How do I go about thanking you?