Chart Data Range - Changes Daily

[Iccreamann21]

Hello Friends!

It's me again. I'm not entirely sure how to explain this in text, but I will give it a try.

So, I do not run my chart off the pivot table because I need to show the grand total first vs last in my chart. <- I was told the grand total couldn't be moved, without using VBA.

Having said that, the quantity of my data changes daily which then requires me to move the data range on my chart.

My initial thought was to return values as blank, but then my charts showed a bump of empty series points. Then I thought, maybe I could return any blanks with #N/A, and then when selecting my data source I could select hidden and empty cell settings and use the "Show #N/A as an empty cell", but that didn't work either......

My end goal would be for me not to have to change the data range daily to accommodate all the data without showing blank or #N/A points.

1. JT - 2024 Data Tracking (FINAL 3).xlsx
	L	M	N	O	P						V	W	X	Y	Z	AA
2	Day3	1/19/2024											PART	TOTAL	DAYS	NIGHTS
3													OXYGEN SENSOR BRACKET	3	3	0
4	Count of Day3	Column Labels											MISSING PART	3	3	0
5	Row Labels	DAYS	NIGHTS	Grand Total									REAR BUMPER	3	3	0
6	OXYGEN SENSOR BRACKET	3	0	3									ELECTRICAL CONNECTION NOT MADE	1	1	0
7	MISSING PART	3	0	3									ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK	2	2	0
8	REAR BUMPER	3	0	3									POWERTRAIN HARNESS	2	2	0
9	ELECTRICAL CONNECTION NOT MADE	1	0	1									SECONDARY LOCK NOT MADE	2	2	0
10	ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK	2	0	2									FRONT AXLE	2	0	2
11	POWERTRAIN HARNESS	2	0	2									ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK	1	0	1
12	SECONDARY LOCK NOT MADE	2	0	2									SECONDARY LOCK NOT MADE	1	0	1
13	FRONT AXLE	0	2	2									FASTENER	2	2	0
14	ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK	0	1	1									MISSING TORQUE	2	2	0
15	SECONDARY LOCK NOT MADE	0	1	1									HEATER RETURN HOSE	2	2	0
16	FASTENER	2	0	2									HOSE NOT FULLY SEATED	2	2	0
17	MISSING TORQUE	2	0	2									#N/A	#N/A	#N/A	#N/A
18	HEATER RETURN HOSE	2	0	2									#N/A	#N/A	#N/A	#N/A
19	HOSE NOT FULLY SEATED	2	0	2									#N/A	#N/A	#N/A	#N/A
20													#N/A	#N/A	#N/A	#N/A
21													#N/A	#N/A	#N/A	#N/A
22													#N/A	#N/A	#N/A	#N/A
23													#N/A	#N/A	#N/A	#N/A
24													#N/A	#N/A	#N/A	#N/A
25													#N/A	#N/A	#N/A	#N/A
26													#N/A	#N/A	#N/A	#N/A
27													#N/A	#N/A	#N/A	#N/A
28													#N/A	#N/A	#N/A	#N/A
29													#N/A	#N/A	#N/A	#N/A
30													#N/A	#N/A	#N/A	#N/A
31													#N/A	#N/A	#N/A	#N/A
32													#N/A	#N/A	#N/A	#N/A
33													#N/A	#N/A	#N/A	#N/A
34													#N/A	#N/A	#N/A	#N/A
35													#N/A	#N/A	#N/A	#N/A
36													#N/A	#N/A	#N/A	#N/A
37													#N/A	#N/A	#N/A	#N/A
2024 Tables

Cell Formulas
Range	Formula
X3:X37	X3	=IF(L6="",#N/A,IF(L6="0",#N/A,IF(L6="","",L6)))
Y3:Y37	Y3	=IF(O6="",#N/A,IF(O6="0",#N/A,IF(O6="","",O6)))
Z3:AA37	Z3	=IF(M6="",#N/A,IF(M6="0",#N/A,IF(M6="","",M6)))

 

[awoohaw]

what are your chart definitions (data range cells)? can you post an image of the chart?

 

[Iccreamann21]

 

[awoohaw]

Thanks. Why use the PIVOT report at all then?
Create your own crosstab and then make a chart after that.
Using the array formulas seems to expand the chart dimensions:

Book1
	X	Y	Z	AA
2	PART	TOTAL	DAYS	NIGHTS
3	OXYGEN SENSOR BRACKET	3	1	2
4	MISSING PART	1	1	0
5	REAR BUMPER	4	1	3
6	ELECTRICAL CONNECTION NOT MADE	3	3	0
7	ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK	1	1	0
8	POWERTRAIN HARNESS	4	1	3
9	SECONDARY LOCK NOT MADE	6	3	3
10	FRONT AXLE	4	1	3
11	ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK	2	2	0
12	SECONDARY LOCK NOT MADE	4	1	3
13	FASTENER	1	0	1
14	MISSING TORQUE	5	2	3
15	HEATER RETURN HOSE	3	1	2
16	HOSE NOT FULLY SEATED	3	1	2
17	Problem 01	5	3	2
18	Problem 02	4	2	2
19	Problem 03	4	2	2
20	Problem 04	3	2	1
21	Problem 05	5	3	2
22	Problem 06	3	3	0
23	Problem 07	5	3	2
24	Problem 08	2	2	0
25	Problem 09	3	2	1
26	Problem 10	3	0	3
27	Problem 11	4	2	2
28	Problem 12	3	3	0
29	Problem 13	6	3	3
30	Problem 14	4	3	1
31	Problem 15	3	0	3
32	Problem 16	4	3	1
33	Problem 17	0	0	0
34	Problem 18	1	0	1
35	Problem 19	5	2	3
36	Problem 20	4	2	2
37	Problem 21	3	1	2
38
39
40
41	PART	TOTAL	DAYS	NIGHTS
42	ELECTRICAL CONNECTION NOT MADE	3	3	0
43	ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK	1	1	0
44	ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK	2	2	0
45	FASTENER	1	0	1
46	FRONT AXLE	4	1	3
47	HEATER RETURN HOSE	3	1	2
48	HOSE NOT FULLY SEATED	3	1	2
49	MISSING PART	1	1	0
50	MISSING TORQUE	5	2	3
51	OXYGEN SENSOR BRACKET	3	1	2
52	POWERTRAIN HARNESS	4	1	3
53	Problem 01	5	3	2
54	Problem 02	4	2	2
55	Problem 03	4	2	2
56	Problem 04	3	2	1
57	Problem 05	5	3	2
58	Problem 06	3	3	0
59	Problem 07	5	3	2
60	Problem 08	2	2	0
61	Problem 09	3	2	1
62	Problem 10	3	0	3
63	Problem 11	4	2	2
64	Problem 12	3	3	0
65	Problem 13	6	3	3
66	Problem 14	4	3	1
67	Problem 15	3	0	3
68	Problem 16	4	3	1
69	Problem 18	1	0	1
70	Problem 19	5	2	3
71	Problem 20	4	2	2
72	Problem 21	3	1	2
73	REAR BUMPER	4	1	3
74	SECONDARY LOCK NOT MADE	6	3	3
75	SECONDARY LOCK NOT MADE	4	1	3
Sheet2

Cell Formulas
Range	Formula
X41:AA75	X41	=LET(dr,$X$3:$AA$37,probs,$X$3:$X$37,days,$Z$3:$Z$37,nights,$AA$3:$AA$37,total,$Y$3:$Y$37,init,FILTER(dr,total<>0,""),sorted,SORT(init,1,1,FALSE),header,$X$2:$AA$2,VSTACK(header,sorted))
Dynamic array formulas.

Chart Data Definition:

 

[Iccreamann21]

I'm using the pivot table to filter the data down to specific parts.

 

[awoohaw]

And??? you can create the cross tab with filters as well.

 

[Iccreamann21]

My apologies, I'm not following as I am unfamiliar with cross tab. It may be worth mentioning that I would also need my chart to stay the same.

Look at my chart above in comparison.

 

[awoohaw]

By cross tab, I mean just formulas in a grid that will look like pivot report.

But, did you even look at what my post formula does? It only selects data that is not zero. Isn't that what you wanted?

The top part is "Data" I could only give summary data, but that makes no difference. But, if you want me to provide a better example please post a xl2bb of your data the pivot is based on (need not post all the data, just enough to give you a reasonable chart). Sanitized for privacy.

The second part at X41 is one forumula that only creates a crosstab report of only data you want in the chart (no zero records).
And if you add any new data between rows 2 and 37 the crosstab and chart should update automatically.

 

[Iccreamann21]

I'm looking for a way to keep my data range the same, but not show #N/A's on my chart.

Monday's Chart Data:

	TOTAL	DAYS	NIGHTS
FUEL TANK	1	0	1
ELECTRICAL CONNECTION NOT MADE	1	0	1
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A

Tuesday's Chart Data:

	TOTAL	DAYS	NIGHTS
RH CHASSIS HARNESS	2	2	0
C-CLIP NOT MADE	1	1	0
X-MAS TREE NOT MADE	1	1	0
MAKE-UP AIR HOSE	2	0	2
C-CLIP NOT MADE/MISSING BLUE MARK	1	0	1
HOSE NOT FULLY SEATED	1	0	1
TRANSFER CASE SHIFT CABLE	1	0	1
GROMMET NOT MADE	1	0	1
LH REAR BRAKE BUNDLE	1	1	0
X-MAS TREE NOT MADE	1	1	0
TRANSMISSION VENT HOSE	1	1	0
C-CLIP NOT MADE	1	1	0
FRONT AXLE VENT HOSE	1	1	0
HOSE NOT FULLY SEATED	1	1	0
POWERTRAIN HARNESS	1	0	1
X-MAS TREE NOT MADE	1	0	1
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A

No Good:

Good:

 

[awoohaw]

I asked for some raw data.
The post I gave you does what you ask, as far as I can tell. Without raw data (the data in the range your pivot table is based on) to construct a crosstab I cannot help you. The formula I created should STRIP out the necessity for any #N/A items.