Chart Data Range - Changes Daily

I

Iccreamann21

New Member
Joined
Sep 25, 2013
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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
LMNOPVWXYZAA
2Day31/19/2024PARTTOTALDAYSNIGHTS
3OXYGEN SENSOR BRACKET330
4Count of Day3Column LabelsMISSING PART330
5Row LabelsDAYSNIGHTSGrand TotalREAR BUMPER330
6OXYGEN SENSOR BRACKET303ELECTRICAL CONNECTION NOT MADE110
7MISSING PART303ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK220
8REAR BUMPER303POWERTRAIN HARNESS220
9ELECTRICAL CONNECTION NOT MADE101SECONDARY LOCK NOT MADE220
10ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK202FRONT AXLE202
11POWERTRAIN HARNESS202ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK101
12SECONDARY LOCK NOT MADE202SECONDARY LOCK NOT MADE101
13FRONT AXLE022FASTENER220
14ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK011MISSING TORQUE220
15SECONDARY LOCK NOT MADE011HEATER RETURN HOSE220
16FASTENER202HOSE NOT FULLY SEATED220
17MISSING TORQUE202#N/A#N/A#N/A#N/A
18HEATER RETURN HOSE202#N/A#N/A#N/A#N/A
19HOSE NOT FULLY SEATED202#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
RangeFormula
X3:X37X3=IF(L6="",#N/A,IF(L6="0",#N/A,IF(L6="","",L6)))
Y3:Y37Y3=IF(O6="",#N/A,IF(O6="0",#N/A,IF(O6="","",O6)))
Z3:AA37Z3=IF(M6="",#N/A,IF(M6="0",#N/A,IF(M6="","",M6)))
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
what are your chart definitions (data range cells)? can you post an image of the chart?
 
Upvote 0
1706629670911.png
 
Upvote 0
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
XYZAA
2PARTTOTALDAYSNIGHTS
3OXYGEN SENSOR BRACKET312
4MISSING PART110
5REAR BUMPER413
6ELECTRICAL CONNECTION NOT MADE330
7ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK110
8POWERTRAIN HARNESS413
9SECONDARY LOCK NOT MADE633
10FRONT AXLE413
11ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK220
12SECONDARY LOCK NOT MADE413
13FASTENER101
14MISSING TORQUE523
15HEATER RETURN HOSE312
16HOSE NOT FULLY SEATED312
17Problem 01532
18Problem 02422
19Problem 03422
20Problem 04321
21Problem 05532
22Problem 06330
23Problem 07532
24Problem 08220
25Problem 09321
26Problem 10303
27Problem 11422
28Problem 12330
29Problem 13633
30Problem 14431
31Problem 15303
32Problem 16431
33Problem 17000
34Problem 18101
35Problem 19523
36Problem 20422
37Problem 21312
38
39
40
41PARTTOTALDAYSNIGHTS
42ELECTRICAL CONNECTION NOT MADE330
43ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK110
44ELECTRICAL CONNECTION NOT MADE/MISSING BLUE MARK220
45FASTENER101
46FRONT AXLE413
47HEATER RETURN HOSE312
48HOSE NOT FULLY SEATED312
49MISSING PART110
50MISSING TORQUE523
51OXYGEN SENSOR BRACKET312
52POWERTRAIN HARNESS413
53Problem 01532
54Problem 02422
55Problem 03422
56Problem 04321
57Problem 05532
58Problem 06330
59Problem 07532
60Problem 08220
61Problem 09321
62Problem 10303
63Problem 11422
64Problem 12330
65Problem 13633
66Problem 14431
67Problem 15303
68Problem 16431
69Problem 18101
70Problem 19523
71Problem 20422
72Problem 21312
73REAR BUMPER413
74SECONDARY LOCK NOT MADE633
75SECONDARY LOCK NOT MADE413
Sheet2
Cell Formulas
RangeFormula
X41:AA75X41=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:
1706643196405.png







1706643032063.png
 
Upvote 0
And??? you can create the cross tab with filters as well.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
TOTALDAYSNIGHTS
FUEL TANK101
ELECTRICAL CONNECTION NOT MADE101
#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:
TOTALDAYSNIGHTS
RH CHASSIS HARNESS220
C-CLIP NOT MADE110
X-MAS TREE NOT MADE110
MAKE-UP AIR HOSE202
C-CLIP NOT MADE/MISSING BLUE MARK101
HOSE NOT FULLY SEATED101
TRANSFER CASE SHIFT CABLE101
GROMMET NOT MADE101
LH REAR BRAKE BUNDLE110
X-MAS TREE NOT MADE110
TRANSMISSION VENT HOSE110
C-CLIP NOT MADE110
FRONT AXLE VENT HOSE110
HOSE NOT FULLY SEATED110
POWERTRAIN HARNESS101
X-MAS TREE NOT MADE101
#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:
1706799054779.png


Good:
1706799113373.png
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Copy non empty cells from the dynamic range column and paste to the bottom of data in table
Replies
0
Views
370
PitMax
P
S
  • Solved
How to combine IF and FILTER formulas to copy text from one column to another
Replies
4
Views
243
Fluff
Fluff
E
  • Locked
  • Question
copying daily changing values from a sheet to another using vba
Replies
5
Views
325
Fluff
Fluff
J
  • Question
Not sure if Excel can do this...
Replies
1
Views
558
Sam_D_Ben
S
B
  • Solved
Firing Pete Best
Replies
2
Views
321
BloodyBill
B

Forum statistics

Threads
1,215,069
Messages
6,122,954
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top