Help creating Formula(s) to track data between sheets

[pip74205]

Sample Document.xlsx

I am trying to simplify my steps when tracking invoices at work. Currently, I have to input the invoice information. Then, I have to track the pricing on two other sheets. What I would like is to be able to input the invoice, and have the totals placed on the other two sheets. My greatest hope (which I do not think is possible), is to have it track within a date range as well. Anyway, on to the question...

I am referencing the attached file here. Sheet 1 is where I am inputting the invoices. Sheets 2 and 3 are where I want the totals per company to be put. As you can see, no prices are placed in Sheet 1 with the invoices. On Sheet 2 and 3, we have the pricing for each part number in the same row as the part type. In Sheets 2 and 3, column K has a purple background. This is the total price of the invoices as I am doing it now. The green background cells are what I want to happen. I want to be able to input the invoice in Sheet 1, and have the total automatically updated in Sheets 2 and 3, as is shown by what the K column would change to in green.

What I didn't input, but would like, is to have separate columns based on a date range. For example: All billing between 10/2/14 and 11/1/14 is in one column per part type, all invoices between 11/2/14 and 12/1/14 are in another, and the next column would be those between 12/2/14 and 1/1/15. I didn't think this was possible, which is why I didn't put it in.

I am fine with however many columns are needed on Sheets 2 and 3 to get this to work. I cannot add the prices in on the invoicing sheet. Please let me know if the invoice part number column (sheet 1) needs to match the price column in sheets 2 and 3. When finished, I want to have it set up so that after inputting the invoice in Sheet 1, the other two sheets will show up with the latest total.

Sample Document.xlsx

[FDibbins]

Hi, welcome to the forum

I think you have some of your numbers a little off in your calc on sheet2?

	A	B	C	D	E	F	G	H	I	J	K	L
1	Price Sheet 1
2	Part Type	Quarter	Half	Single	Whole	Lever	Rod	Plate	Connector	Fold	TOTAL
3	101	$25.00	$50.00	$100.00	$200.00	$75.00	$15.00	$650.00	$5.00	$12.50	$ 870.00	$1,550.00
4	202	$20.00	$45.00	$90.00	$150.00	$60.00	$20.00	$500.00	$7.75	$15.00	$ 873.00
5	105	$45.00	$45.00	$125.00	$150.00	$25.00	$10.00	$1,000.00	$35.00	$80.00	$ 500.00
6
7		1	2	2	1	0	3	0	4	0
8		$25.00	$100.00	$200.00	$200.00	$0.00	$45.00	$0.00	$20.00	$0.00	$590.00
9										$ 590.00

Rows 7 and 8 are just to test.
B7=SUMIFS(Sheet1!$E$2:$E$19,Sheet1!$A$2:$A$19,$A3,Sheet1!$F$2:$F$19,B$2)
B8=SUMIFS(Sheet1!$E$2:$E$19,Sheet1!$A$2:$A$19,$A3,Sheet1!$F$2:$F$19,B$2)*B3

J7=SUMPRODUCT((Sheet1!$A$2:$A$19=Sheet2!A$3)*(Sheet1!$F$2:$F$19=Sheet2!$B$2:$J$2)*Sheet1!$E$2:$E$19*Sheet2!B3:J3)

We can use this as the start. From here we can include date ranges, and (I didnt figure out how to ID this from yoyr sample) which sheet to use

[pip74205]

This looks like it will work. Thank you very much. I am not marking it SOLVED yet, as you said there is a way to add in date ranges. As your equation focused on Part type 101, lets keep using that one. Let's use the date ranges of 10/7 to 11/6, 11/7 to 12/6, and 12/7 to 1/6. As Part type 101 has 5 unique dates, that will put 10/9 in one date range; 11/10, 11/26, and 12/5 in the next date range, and 12/18 in the last date range.

Again, thank you for the help. My abilities in Excel end at more than the basic equations.

[FDibbins]

Im not sure where you want these answers to end up, or where you will be putting the dates, but this gives an example of how you would pull in the dates for what you want (I only used the 1st set of dates you gave, and put them in A7:A8)...

	A	B	C	D	E	F	G	H	I	J
1	Price Sheet 1
2	Part Type	Quarter	Half	Single	Whole	Lever	Rod	Plate	Connector	Fold
3	101	$25.00	$50.00	$100.00	$200.00	$75.00	$15.00	$650.00	$5.00	$12.50
4	202	$20.00	$45.00	$90.00	$150.00	$60.00	$20.00	$500.00	$7.75	$15.00
5	105	$45.00	$45.00	$125.00	$150.00	$25.00	$10.00	$1,000.00	$35.00	$80.00
6
7	7-Oct	$25.00	$100.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
8	1-Nov	$40.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
9		$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00

B7=SUMIFS(Sheet1!$E$2:$E$19,Sheet1!$A$2:$A$19,$A3,Sheet1!$F$2:$F$19,B$2,Sheet1!$D$2:$D$19,">="&Sheet2!$A$7,Sheet1!$D$2:$D$19,"<="&Sheet2!$A$8)*B3
copied down and across

You can see where I added the date criteria in the bolded part

[pip74205]

Could you double check the formula that you posted? I attempted to translate it, adding in the bolded section with the appropriate cells referenced, but it doesn't seem to be working. Could you save your changes and upload them in the program, so I can see what you did. Thanks again for your assistance. This complex of a formula is usually above my head.

[FDibbins]

Did you add the dates that I used in A7:A8? without those, all the answers will be 0