Invoice Data Reporting

[Discipline]

So I have been tasked with creating an invoice tracking sheet, GREAT!! Love Excel, fairly descent at it, but this escapes me. They want Sheet 1 for all to be invoice data by date, then Sheet 2 for reporting. I understand all that but cant figure out how to et the data from Sheet 1 to Sheet 2. Just looking for simple help.

Here is what I have so far from Sheet 1:

DATE

INVOICE NUMBER

JOB DESCRIPTION

JOB DESCRIPTION

TYPE

RETAIL MANAGER ON DUTY

1ST TECH NAME

2ND TECH NAME

INVOICE PRICE

LABOR CHARGED

APPRENTICE NAME

TIRE TRADE IN

FORM OF PAYMENT

# OF TECHS

START TIME

STOP TIME

JOB TIME

TOTAL TIME

11/3/20

800215

Suspension System

Mechanical

Jak

Stan

$379.58

$79.99

0

TCE (Tire Care Express)

1

19:58

22:12

2:14

2:14

10/1/20

800105

Tire Repair

Tires

adam

$23.55

$18.56

0

TCE (Tire Care Express)

1

3:54

4:20

0:26

0:26

10/1/20

800108

Reversal

0

0:00

0:00

10/1/20

800106

Err Reject

0

0:00

0:00

10/1/20

800107

Parts

$31.45

Fleet Card

0

0:00

0:00

10/1/20

800104

2 Tire Sale

Tires

brandon

$694.37

$104.73

0

TCE (Tire Care Express)

1

9:16

11:00

1:44

1:44

10/1/20

800109

Void

0

0:00

0:00

10/1/20

800110

1 Tire Sale

Tires

brandon

National Account

1

11:10

13:08

1:58

1:58

10/1/20

800102

Parts

$41.95

Credit Card

0

0:00

0:00

10/2/20

800117

1 Tire Sale

Road Call

stan

0

National Account

1

8:55

11:45

2:50

2:50

10/2/20

800119

Tire Repair

Tires

zeke

chris c

$97.84

$92.96

0

Credit Card

2

14:59

17:44

2:45

5:30

10/2/20

800116

Mount/Dismount

Tires

trevor

$108.18

$79.99

0

Check

1

10:58

12:00

1:02

1:02

10/2/20

800111

1 Tire Sale

Mount/Dismount

Tires

eric

isaac

0

National Account

2

8:45

10:55

2:10

4:20

11/2/20

800153

Parts

isaac

$26.20

Cash

1

0:00

0:00

11/2/20

800150

2 Tire Sale

Tires

$960.75

$79.98

0

Credit Card

0

0:00

0:00

11/2/20

800159

1 Tire Sale

Tires

National Account

0

0:00

0:00

11/2/20

800154

Cooling System

Mechanical

jak

isaac

$31.56

$29.99

Credit Card

1

8:35

8:50

0:15

0:15

11/2/20

800152

Other

Parts

jak

chris

$15.99

$15.99

Credit Card

1

10:01

10:22

0:21

0:21

11/2/20

800160

Electrical System

Mechanical

eric

$32.57

$25.00

National Account

1

15:13

15:55

0:42

0:42

11/2/20

800157

Mount/Dismount

Tires

stan

$73.65

$69.98

0

Credit Card

1

19:35

21:00

1:25

1:25

11/5/20

800156

1 Tire Sale

Tires

brandon

$298.94

$39.99

0

Credit Card

1

9:00

9:34

0:34

0:34

11/5/20

800155

1 Tire Sale

Tires

brandon

$624.82

$40.00

0

National Account

1

12:00

12:30

0:30

0:30

11/5/20

800149

Other

Parts

forklift

$52.02

$27.50

TCE (Tire Care Express)

1

12:34

12:58

0:24

0:24

11/5/20

800146

Braking System

Mechanical

Jak

doug

$28.41

$26.99

TCE (Tire Care Express)

1

16:03

16:23

0:20

0:20

11/5/20

800148

Oil Change

Mechanical

eddie

doug

$372.93

$79.00

zeke

Credit Card

2

17:30

18:30

1:00

2:00

11/6/20

800162

Other

Retail

stan

$251.74

Credit Card

1

0:00

0:00

This is what they need for the reports from Sheet 2:

DATE	Road Call	Part Sales		Mechanic Sales		Tire Sales		INVOICE PRICE	LABOR CHARGED	TOTAL TIME
Quantity		Quantity	Sale Price	Quantity	Sale Price	Quantity	Sale Price
10/1/2020​
10/2/2020​
10/3/2020​
10/4/2020​
11/1/2020​
11/2/2020​
11/3/2020​
11/4/2020​
11/5/2020​
11/6/2020​
11/7/2020​
11/8/2020​

Any assistance would be greatly appreciated. Thanks

 

[Crystalyzer]

Is sheet 2 supposed to be a summary of individual invoices or a summary of ALL invoices in each of the columns by date? Can you post an example of what you are looking for with data manually entered?

 

[pjmorris]

I think I'd import it to Powerquery (New Query, From Table, on Get & Transform tab of the Data ribbon). You can manipulate the data as much as you like and then send it to Sheet 2 as a new table. Whenever the first table changes simply refresh data.

I might also ask them whether they'd pay to do a proper excel course - it can do so much more than many people realise and a course will quickly get you upto speed.

 

[Discipline]

So I have been tasked with creating an invoice tracking sheet, GREAT!! Love Excel, fairly descent at it, but this escapes me. They want Sheet 1 for all to be invoice data by date, then Sheet 2 for reporting. I understand all that but cant figure out how to et the data from Sheet 1 to Sheet 2. Just looking for simple help.

Here is what I have so far from Sheet 1:

DATE

INVOICE NUMBER

JOB DESCRIPTION

JOB DESCRIPTION

TYPE

RETAIL MANAGER ON DUTY

1ST TECH NAME

2ND TECH NAME

INVOICE PRICE

LABOR CHARGED

APPRENTICE NAME

TIRE TRADE IN

FORM OF PAYMENT

# OF TECHS

START TIME

STOP TIME

JOB TIME

TOTAL TIME

11/3/20

800215

Suspension System

Mechanical

Jak

Stan

$379.58

$79.99

0

TCE (Tire Care Express)

1

19:58

22:12

2:14

2:14

10/1/20

800105

Tire Repair

Tires

adam

$23.55

$18.56

0

TCE (Tire Care Express)

1

3:54

4:20

0:26

0:26

10/1/20

800108

Reversal

0

0:00

0:00

10/1/20

800106

Err Reject

0

0:00

0:00

10/1/20

800107

Parts

$31.45

Fleet Card

0

0:00

0:00

10/1/20

800104

2 Tire Sale

Tires

brandon

$694.37

$104.73

0

TCE (Tire Care Express)

1

9:16

11:00

1:44

1:44

10/1/20

800109

Void

0

0:00

0:00

10/1/20

800110

1 Tire Sale

Tires

brandon

National Account

1

11:10

13:08

1:58

1:58

10/1/20

800102

Parts

$41.95

Credit Card

0

0:00

0:00

10/2/20

800117

1 Tire Sale

Road Call

stan

0

National Account

1

8:55

11:45

2:50

2:50

10/2/20

800119

Tire Repair

Tires

zeke

chris c

$97.84

$92.96

0

Credit Card

2

14:59

17:44

2:45

5:30

10/2/20

800116

Mount/Dismount

Tires

trevor

$108.18

$79.99

0

Check

1

10:58

12:00

1:02

1:02

10/2/20

800111

1 Tire Sale

Mount/Dismount

Tires

eric

isaac

0

National Account

2

8:45

10:55

2:10

4:20

11/2/20

800153

Parts

isaac

$26.20

Cash

1

0:00

0:00

11/2/20

800150

2 Tire Sale

Tires

$960.75

$79.98

0

Credit Card

0

0:00

0:00

11/2/20

800159

1 Tire Sale

Tires

National Account

0

0:00

0:00

11/2/20

800154

Cooling System

Mechanical

jak

isaac

$31.56

$29.99

Credit Card

1

8:35

8:50

0:15

0:15

11/2/20

800152

Other

Parts

jak

chris

$15.99

$15.99

Credit Card

1

10:01

10:22

0:21

0:21

11/2/20

800160

Electrical System

Mechanical

eric

$32.57

$25.00

National Account

1

15:13

15:55

0:42

0:42

11/2/20

800157

Mount/Dismount

Tires

stan

$73.65

$69.98

0

Credit Card

1

19:35

21:00

1:25

1:25

11/5/20

800156

1 Tire Sale

Tires

brandon

$298.94

$39.99

0

Credit Card

1

9:00

9:34

0:34

0:34

11/5/20

800155

1 Tire Sale

Tires

brandon

$624.82

$40.00

0

National Account

1

12:00

12:30

0:30

0:30

11/5/20

800149

Other

Parts

forklift

$52.02

$27.50

TCE (Tire Care Express)

1

12:34

12:58

0:24

0:24

11/5/20

800146

Braking System

Mechanical

Jak

doug

$28.41

$26.99

TCE (Tire Care Express)

1

16:03

16:23

0:20

0:20

11/5/20

800148

Oil Change

Mechanical

eddie

doug

$372.93

$79.00

zeke

Credit Card

2

17:30

18:30

1:00

2:00

11/6/20

800162

Other

Retail

stan

$251.74

Credit Card

1

0:00

0:00

This is what they need for the reports from Sheet 2:

DATE	Road Call	Part Sales		Mechanic Sales		Tire Sales		INVOICE PRICE	LABOR CHARGED	TOTAL TIME
Quantity		Quantity	Sale Price	Quantity	Sale Price	Quantity	Sale Price
10/1/2020​
10/2/2020​
10/3/2020​
10/4/2020​
11/1/2020​
11/2/2020​
11/3/2020​
11/4/2020​
11/5/2020​
11/6/2020​
11/7/2020​
11/8/2020​

Any assistance would be greatly appreciated. Thanks

Here is what it should look like:

DATE	Road Call	Part Sales	Mechanic Sales	Tire Sales	INVOICE PRICE	LABOR CHARGED	TOTAL TIME
Quantity	Sale Price	Quantity	Sale Price	Quantity	Sale Price
10/1/2020​		2	$73.40			3	$717.92	$791.32	$123.29	4:08
10/2/2020​	1					4	$206.20	$206.02	$172.95	13:42
10/3/2020​
10/4/2020​
11/1/2020​
11/2/2020​		2	$42.19	2	$64.13	3	$1,034.40	$1,140.72	$220.94	2:43
11/3/2020​
11/4/2020​
11/5/2020​		1	$52.02	2	$401.34	2	$923.76	$1,377.12	$213.48	3:48
11/6/2020​		1	$251.74					$251.74

 

[Crystalyzer]

I converted your Sheet1 data to a table to simplify the formulas and allow you to add new rows without having to update formulas on Sheet2. I assumed that Parts = Parts + Retail (this seemed to prove out). Your headers are a bit confusing but here is what I managed to cull together. Also, I inserted a helper row at the top for the criteria for the columns and a helper column to the right so you can filter out rows with no data.

It should be a good starting point for you. Good luck!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Helper Row	Road Call	Parts	Retail	Mechanical		Tires
2	Date	Road Call	Parts		Mechanic Sales		Tire Sales		Invoice Price	Labor Charged	Total Time
3			Quantity	Sale Price	Quantity	Sale Price	Quantity	Sale Price				Filter Blanks
4	10/1/2020		2	$73.40			3	$717.92	$791.32	123.29	4:08	1711.1022
5	10/2/2020	1					3	$206.02	$206.02	172.95	13:42	589.56083
36	11/2/2020		2	$42.19	2	$54.99	3	$1,034.40	$1,131.58	220.94	2:43	2491.2132
37	11/3/2020				1	$79.99			$79.99	79.99	2:14	241.06306
39	11/5/2020		1	$52.02	2	$105.99	2	$923.76	$1,081.77	213.48	3:48	2382.1783
40	11/6/2020		1	$251.74					$251.74			504.48
Sheet2

Cell Formulas
Range	Formula
B4	B4	=IF(COUNTIFS(Table1[Type],"="&Sheet2!B$2,Table1[Date],"="&Sheet2!$A4)=0,"",COUNTIFS(Table1[Type],"="&Sheet2!B$2,Table1[Date],"="&Sheet2!$A4))
C4:C5,E4:E5,C36:C37,E36:E37,C39:C40,E39:E40	C4	=IF(COUNTIFS(Table1[Type],"="&Sheet2!C$1,Table1[Date],"="&Sheet2!$A4)+COUNTIFS(Table1[Type],"="&Sheet2!D$1,Table1[Date],"="&Sheet2!$A4)=0,"",COUNTIFS(Table1[Type],"="&Sheet2!C$1,Table1[Date],"="&Sheet2!$A4)++COUNTIFS(Table1[Type],"="&Sheet2!D$1,Table1[Date],"="&Sheet2!$A4))
D4:D5,D36:D37,D39:D40	D4	=IF(SUMIFS(Table1[Invoice Price],Table1[Type],"="&Sheet2!$C$1,Table1[Date],"="&Sheet2!$A4)+SUMIFS(Table1[Invoice Price],Table1[Type],"="&Sheet2!$D$1,Table1[Date],"="&Sheet2!$A4)=0,"",SUMIFS(Table1[Invoice Price],Table1[Type],"="&Sheet2!$C$1,Table1[Date],"="&Sheet2!$A4)+SUMIFS(Table1[Invoice Price],Table1[Type],"="&Sheet2!$D$1,Table1[Date],"="&Sheet2!$A4))
F4:F5,F36:F37,F39:F40	F4	=IF(SUMIFS(Table1[Labor Charged],Table1[Type],"="&Sheet2!$E$1,Table1[Date],"="&Sheet2!$A4)=0,"",SUMIFS(Table1[Labor Charged],Table1[Type],"="&Sheet2!$E$1,Table1[Date],"="&Sheet2!$A4))
G4:G5,G36:G37,G39:G40	G4	=IF(COUNTIFS(Table1[Type],"="&Sheet2!G$1,Table1[Date],"="&Sheet2!$A4)=0,"",COUNTIFS(Table1[Type],"="&Sheet2!G$1,Table1[Date],"="&Sheet2!$A4))
H4:H5,H36:H37,H39:H40	H4	=IF(SUMIFS(Table1[Invoice Price],Table1[Type],"="&Sheet2!$G$1,Table1[Date],"="&Sheet2!$A4)=0,"",SUMIFS(Table1[Invoice Price],Table1[Type],"="&Sheet2!$G$1,Table1[Date],"="&Sheet2!$A4))
I4:I5,I36:I37,I39:I40	I4	=IF(SUM(H4,F4,D4)=0,"",SUM(H4,F4,D4))
J4:J5,J36:J37,J39:J40	J4	=IF(SUMIF(Table1[Date],"="&Sheet2!$A4,Table1[Labor Charged])=0,"",SUMIF(Table1[Date],"="&Sheet2!$A4,Table1[Labor Charged]))
K4:K5,K36:K37,K39:K40	K4	=IF(SUMIF(Table1[Date],"="&Sheet2!$A4,Table1[Total Time])=0,"",SUMIF(Table1[Date],"="&Sheet2!$A4,Table1[Total Time]))
L4:L5,L36:L37,L39:L40	L4	=SUM(B4:K4)
B5,B36:B37,B39:B40	B5	=IF(COUNTIFS(Table1[Type],"="&Sheet2!$B$2,Table1[Date],"="&Sheet2!$A5)=0,"",COUNTIFS(Table1[Type],"="&Sheet2!$B$2,Table1[Date],"="&Sheet2!$A5))

 

[Discipline]

THANKS FOR THE HELP!!!!

Supervisors liked the new Tracker, all the credit went to yall. I took none of it.

Now here comes the hard part of asking for more help.

Now they would like to see other tabs filled out for a Training Tracker and Mechanic Tracker, all that information will be from the Primary Invoice tab.

Here is the information from the Invoice Tab:

A	B	C	D	E	F	G	H
DATE	INVOICE #	TECH #1	TECH #2	INVOICE TOTAL	LABOR CHARGED	START TIME	STOP TIME
11/1/20	644017120	stan		$365.76	$79.98	11:45	12:45
11/1/20	644017118	stan		$73.65	$69.98	8:00	9:37
11/1/20	644017123	stan				14:15	16:30
11/1/20	644017122	chris c		$466.83	$39.99	12:55
11/1/20	644017125	zeke		$495.04	$40.00	16:38	18:10
11/1/20	644017117	eric		$31.56	$29.99
11/1/20	644017119	eric		$372.93	$79.99	9:30	11:20
11/1/20	644017121	eric		$376.29	$296.97	11:00	15:10
11/1/20	644017126	doug		$776.66	$318.98	18:30	21:30
11/2/20	644017132	isaac		$26.20
11/2/20	644017133			$960.75	$79.98
11/2/20	644017135					23:45	0:55
11/2/20	644018137	stan		$73.65	$69.98	19:35	21:00
11/2/20	644017129	isaac		$31.56	$29.99	8:35	8:50

Here is the information they need on the Training Tracker: Stan in the Trainee

A	B	C
Date	Time	Invoice #

Note: The Time column is the Start Time from the Invoice Tab.

Here is the information they need on the Mechanics Tracker: Eric is the Mechanic

A	B	C	D	E	F	G	H	I	J	K
Date	INV#	Start time	Job Class	Description	Parts in Stock	Stop Time	Job Time	Labor Charged	Comm Rate	Net Commission Paid
									20%
									20%
									20%
									20%
									20%

Note: The following columns are completed by the mechanic: Job Class, Description, Parts in Stock.
Job Time, Comm Rate and Net Commission do not need input from reference tabs.

Thanks in advance, I really do give the credit all to you and appreciate the assistance with this. I have been learning alot reading other operators posts.