Price tracking

mykeee

New Member
Joined
Sep 9, 2010
Messages
46
I would like to create a spreadsheet that tracks our material costs over time. I would like to have a chart that is easy to select between materials and shows the trend of the pricing (like a dashboard), and possibly something that multiple people in our organization can access. I was thinking possibly a pivot table/chart, but don't have the expertise to organize it to look pretty. ;) I've attached what I have started with. Any suggestions would be appreciated. Really want to impress the boss. lol

Material Pricing Tracker.xlsx
ABCDEF
3Part #MaterialVendor Price/FT PO #Date
4HRIB1022W10 x 22 Wide Flange BeamBrunswick$ 26.452022-04-20
5HRC34C3 x 4.1 HR ChannelBrunswick$ 4.942022-04-20
6HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 10.562022-04-20
7HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMcNeilus$ 9.202022-04-20
8HRST2122121882-1/2 x 2-1/2 x .188w HR TubeRussel$ 8.982022-04-20
9HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMarmon Keystone$ 8.802022-04-20
10HRST2122121882-1/2 x 2-1/2 x .188w HR TubeEncore$ 13.852022-04-20
11HRST2122121882-1/2 x 2-1/2 x .188w HR TubeRyerson$ 10.402022-04-20
12HRST1121121881-1/2 x 1-1/2 x .188w HR TubeBrunswick$ 2.152022-04-20
13HRST1121121881-1/2 x 1-1/2 x .188w HR TubeMcNeilus$ 4.992022-04-20
14HRST1121121881-1/2 x 1-1/2 x .188w HR TubeRussel$ 4.882022-04-20
15HRST1121121881-1/2 x 1-1/2 x .188w HR TubeMarmon Keystone$ 4.802022-04-20
16HRST1121121881-1/2 x 1-1/2 x .188w HR TubeEncore$ 7.522022-04-20
17HRST1121121881-1/2 x 1-1/2 x .188w HR TubeRyerson$ 6.632022-04-20
18HRST111001 x 1 x .100w HR TubeBrunswick$ 2.152022-04-20
19HRST111001 x 1 x .100w HR TubeMcNeilus$ 2.212022-04-20
20HRST111001 x 1 x .100w HR TubeRussel$ 1.832022-04-20
21HRST111001 x 1 x .100w HR TubeMarmon Keystone$ 1.85264742022-04-20
22HRF1421/4 x 2 HR Flat BarMcNeilus$ 1.772022-04-20
23HRF1421/4 x 2 HR Flat BarRussel$ 1.642022-04-20
24HRF1421/4 x 2 HR Flat BarRyerson$ 2.242022-04-20
25HRF1261/2 x 6 HR Flat BarBrunswick$ 11.712022-04-20
26HRF1261/2 x 6 HR Flat BarMcNeilus$ 10.482022-04-20
27HRF1261/2 x 6 HR Flat BarRussel$ 9.80264762022-04-20
28HRF1261/2 x 6 HR Flat BarRyerson$ 12.952022-04-20
29HRA33143 x 3 x 1/4 HR AngleBrunswick$ 5.622022-04-20
30HRA33143 x 3 x 1/4 HR AngleMcNeilus$ 5.032022-04-20
31HRA33143 x 3 x 1/4 HR AngleRussel$ 4.71264762022-04-20
32HRA33143 x 3 x 1/4 HR AngleRyerson$ 6.032022-04-20
33CRR101858CR-1018 5/8" Round BarBrunswick$ 2.422022-04-20
34CRR101858CR-1018 5/8" Round BarMcNeilus$ 1.552022-04-20
35CRR101858CR-1018 5/8" Round BarRussel$ 1.51264762022-04-20
36CRR101858CR-1018 5/8" Round BarEncore$ 1.982022-04-20
37CRR101858CR-1018 5/8" Round BarRyerson$ 2.162022-04-20
38CRR104534CR-1045 3/4" Round BarMcNeilus$ 2.252022-04-20
39CRR104534CR-1045 3/4" Round BarRussel$ 2.18264762022-04-20
40CRR104534CR-1045 3/4" Round BarEncore$ 2.292022-04-20
41CRR104534CR-1045 3/4" Round BarRyerson$ 2.492022-04-20
42HRRT321883 x 2 x .188w HR TubeMarmon Keystone$ 8.80264742022-04-20
43HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMarmon Keystone$ 8.80264742022-04-20
44CRR104534CR-1045 3/4" Round BarBrunswick$ 1.742022-04-20
45CRR101834CR-1018 3/4" Round BarRussel$ 2.182022-04-20
46CRR10451CR-1045 1" Round ShaftRyerson$ 4.502022-04-20
47CRR1045118CR-1045 1-1/8" Round ShaftRyerson$ 5.742022-04-20
48CRR1045138CR-1045 1-3/8" Round ShaftRyerson$ 8.452022-04-20
49CRR1045134CR-1045 1-3/4" Round ShaftRyerson$ 12.662022-04-20
50CRR10452CR-1045 2" Round ShaftRyerson$ 16.942022-04-20
51HRIB1022W10 x 22 Wide Flange BeamVarsteel$ 19.36238592021-08-18
52HRIB1022W10 x 22 Wide Flange BeamVarsteel$ 17.37223842021-04-20
53HRIB1022W10 x 22 Wide Flange BeamBrunswick$ 13.40206502020-12-04
54HRIB1022W10 x 22 Wide Flange BeamRussel$ 12.74205192020-11-25
55HRIB1022W10 x 22 Wide Flange BeamBrunswick$ 12.93203992020-11-13
56HRIB1022W10 x 22 Wide Flange BeamBrunswick$ 12.85192792020-08-12
57HRIB1022W10 x 22 Wide Flange BeamMcNeilus$ 17.56156622019-07-03
58HRIB1022W10 x 22 Wide Flange BeamVarsteel$ 13.86156452019-07-02
59HRIB1022W10 x 22 Wide Flange BeamRussel$ 15.68152122019-05-17
60HRIB1022W10 x 22 Wide Flange BeamBrunswick$ 15.68151342019-05-13
61HRC34C3 x 4.1 HR ChannelMcNeilus$ 4.20264602022-04-20
62HRC34C3 x 4.1 HR ChannelBrunswick$ 4.82262182022-03-28
63HRC34C3 x 4.1 HR ChannelRussel$ 3.18229812021-06-04
64HRC34C3 x 4.1 HR ChannelVarsteel$ 3.03223842021-04-20
65HRC34C3 x 4.1 HR ChannelBrunswick$ 2.97221812021-04-05
66HRC34C3 x 4.1 HR ChannelMcNeilus$ 3.01217752021-03-08
67HRC34C3 x 4.1 HR ChannelBrunswick$ 2.46206122020-12-02
68HRC34C3 x 4.1 HR ChannelBrunswick$ 2.46201072020-10-22
69HRC34C3 x 4.1 HR ChannelRussel$ 2.46183022020-05-20
70HRC34C3 x 4.1 HR ChannelBrunswick$ 2.57173592020-02-18
71HRC34C3 x 4.1 HR ChannelBrunswick$ 2.57166522019-10-25
72HRC34C3 x 4.1 HR ChannelRussel$ 2.57165442019-10-03
73HRC34C3 x 4.1 HR ChannelBrunswick$ 2.59157882019-07-18
74HRC34C3 x 4.1 HR ChannelBrunswick$ 2.60156892019-07-05
75HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 7.78255412022-01-24
76HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMcNeilus$ 9.74242722021-09-20
77HRST2122121882-1/2 x 2-1/2 x .188w HR TubeVarsteel$ 9.42235422021-07-23
78HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 9.16233642021-07-07
79HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMcNeilus$ 7.94226202021-05-05
80HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMcNeilus$ 7.51225322021-04-29
81HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 7.97224312021-04-22
82HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMcNeilus$ 7.52223852021-04-20
83HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 7.42221182021-03-29
84HRST2122121882-1/2 x 2-1/2 x .188w HR TubeRussel$ 6.12215652021-02-19
85HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 6.61212732021-01-28
86HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMarmon Keystone$ 6.88212502021-01-27
87HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMarmon Keystone$ 6.88212122021-01-26
88HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMcNeilus$ 6.14211342021-01-20
89HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMarmon Keystone$ 5.80210742021-01-14
90HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMarmon Keystone$ 5.80209962021-01-08
91HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 4.90209412021-01-05
92HRST2122121882-1/2 x 2-1/2 x .188w HR TubeRussel$ 5.18209402021-01-05
93HRST2122121882-1/2 x 2-1/2 x .188w HR TubeRussel$ 4.56207142020-12-10
94HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 3.99203612020-11-10
95HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMcNeilus$ 3.83201792020-10-28
96HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMcNeilus$ 3.26196952020-09-18
97HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 3.42194022020-08-26
98HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 3.42191892020-08-04
99HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMcNeilus$ 3.58189662020-07-14
100HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 3.42188242020-07-02
101HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 3.56181552020-05-06
102HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 3.56176992020-04-02
103HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMarmon Keystone$ 3.70175732020-03-18
104HRST2122121882-1/2 x 2-1/2 x .188w HR TubeMarmon Keystone$ 3.70174932020-03-06
105HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 3.51169322019-12-11
106HRST2122121882-1/2 x 2-1/2 x .188w HR TubeRussel$ 3.99154832019-06-17
107HRST2122121882-1/2 x 2-1/2 x .188w HR TubeRussel$ 3.98153792019-06-06
108HRST2122121882-1/2 x 2-1/2 x .188w HR TubeBrunswick$ 3.98153472019-06-04
109HRST2122121882-1/2 x 2-1/2 x .188w HR TubeRussel$ 3.99153382019-12-06
110HRST111001 x 1 x .100w HR TubeRussel$ 1.64262562022-03-31
111HRST111001 x 1 x .100w HR TubeRussel$ 2.00247512021-10-25
112HRST111001 x 1 x .100w HR TubeRussel$ 1.95246872021-10-19
113HRST111001 x 1 x .100w HR TubeBrunswick$ 2.00242492021-09-17
114HRST111001 x 1 x .100w HR TubeRussel$ 1.64229812021-06-04
115HRST111001 x 1 x .100w HR TubeBrunswick$ 1.77228392021-05-26
116HRST111001 x 1 x .100w HR TubeBrunswick$ 1.62226572021-05-06
117HRST111001 x 1 x .100w HR TubeBrunswick$ 1.62223402021-04-15
118HRST111001 x 1 x .100w HR TubeBrunswick$ 1.53220712021-03-26
119HRST111001 x 1 x .100w HR TubeBrunswick$ 1.55219892021-03-23
120HRST111001 x 1 x .100w HR TubeBrunswick$ 1.54219602021-03-18
121HRST111001 x 1 x .100w HR TubeRussel$ 1.25215912021-02-23
122HRST111001 x 1 x .100w HR TubeBrunswick$ 1.40215752021-02-22
123HRST111001 x 1 x .100w HR TubeBrunswick$ 1.34212112021-01-26
124HRST111001 x 1 x .100w HR TubeMcNeilus$ 1.38211342021-01-20
125HRST111001 x 1 x .100w HR TubeBrunswick$ 0.80208802020-12-22
126HRST111001 x 1 x .100w HR TubeRussel$ 0.80205192020-11-25
127HRST111001 x 1 x .100w HR TubeBrunswick$ 0.82203612020-11-10
128HRST111001 x 1 x .100w HR TubeBrunswick$ 0.68201492020-10-26
129HRST111001 x 1 x .100w HR TubeRussel$ 0.68195572020-09-10
130HRST111001 x 1 x .100w HR TubeBrunswick$ 0.69192792020-08-12
131HRST111001 x 1 x .100w HR TubeBrunswick$ 0.70190022020-07-16
132HRST111001 x 1 x .100w HR TubeBrunswick$ 0.70188242020-07-02
133HRST111001 x 1 x .100w HR TubeBrunswick$ 0.75183092020-05-21
134HRST111001 x 1 x .100w HR TubeVarsteel$ 0.77177862020-04-09
135HRST111001 x 1 x .100w HR TubeVarsteel$ 0.71176902020-04-01
136HRST111001 x 1 x .100w HR TubeVarsteel$ 0.71174082020-02-25
137HRST111001 x 1 x .100w HR TubeRussel$ 0.71170632020-01-09
138HRST111001 x 1 x .100w HR TubeBrunswick$ 0.73169812019-12-18
139HRST111001 x 1 x .100w HR TubeBrunswick$ 0.73168822019-12-03
140HRST111001 x 1 x .100w HR TubeRussel$ 0.75165442019-10-03
141HRST111001 x 1 x .100w HR TubeBrunswick$ 0.95160752019-08-14
142HRF1421/4 x 2 HR Flat BarRussel$ 1.64264762022-04-20
143HRF1421/4 x 2 HR Flat BarBrunswick$ 1.55242492021-09-17
144HRF1421/4 x 2 HR Flat BarMcNeilus$ 1.53235152021-07-21
145HRF1421/4 x 2 HR Flat BarBrunswick$ 1.45231192021-06-17
146HRF1421/4 x 2 HR Flat BarBrunswick$ 1.31220712021-03-26
147HRF1421/4 x 2 HR Flat BarRussel$ 1.16212312021-01-27
148HRF1421/4 x 2 HR Flat BarMcNeilus$ 0.99196952020-09-18
149HRF1421/4 x 2 HR Flat BarRussel$ 0.99195572020-09-10
150HRF1421/4 x 2 HR Flat BarBrunswick$ 1.02186872020-06-23
151HRF1421/4 x 2 HR Flat BarBrunswick$ 1.21184372020-06-02
152HRF1421/4 x 2 HR Flat BarBrunswick$ 1.21173592020-02-18
153HRF1421/4 x 2 HR Flat BarBrunswick$ 1.06169812019-12-18
154HRF1421/4 x 2 HR Flat BarRussel$ 1.06158202019-07-23
155HRF1421/4 x 2 HR Flat BarBrunswick$ 1.02154542019-06-13
156HRF1261/2 x 6 HR Flat BarBrunswick$ 7.03210152021-01-11
157HRF1261/2 x 6 HR Flat BarBrunswick$ 6.30176712020-03-31
158HRF1261/2 x 6 HR Flat BarVarsteel$ 6.34176082020-03-23
159HRF1261/2 x 6 HR Flat BarBrunswick$ 6.23157882019-07-18
160HRA33143 x 3 x 1/4 HR AngleBrunswick$ 4.52248612021-11-04
161HRA33143 x 3 x 1/4 HR AngleBrunswick$ 3.72221662021-04-01
162HRA33143 x 3 x 1/4 HR AngleBrunswick$ 2.85201072020-10-22
163HRA33143 x 3 x 1/4 HR AngleRussel$ 2.85195572020-09-10
164HRA33143 x 3 x 1/4 HR AngleMcNeilus$ 2.69185092020-06-09
165HRA33143 x 3 x 1/4 HR AngleBrunswick$ 2.92173972020-02-24
166HRA33143 x 3 x 1/4 HR AngleBrunswick$ 2.92172032020-01-28
167HRA33143 x 3 x 1/4 HR AngleBrunswick$ 2.98171372020-01-21
168HRA33143 x 3 x 1/4 HR AngleVarsteel$ 2.98158312019-07-24
169CRR101858CR-1018 5/8" Round BarRussel$ 1.14181282020-05-05
170CRR101858CR-1018 5/8" Round BarRussel$ 1.15159472019-07-31
171CRR101858CR-1018 5/8" Round BarRussel$ 1.15157662019-07-17
172CRR104534CR-1045 3/4" Round BarEncore$ 2.50173112020-02-11
173CRR104534CR-1045 3/4" Round BarRussel$ 1.83150512019-05-06
174HRRT321883 x 2 x .188w HR TubeBrunswick$ 7.78255412022-01-24
175HRRT321883 x 2 x .188w HR TubeMcNeilus$ 9.74242722021-09-20
176HRRT321883 x 2 x .188w HR TubeRussel$ 8.04229812021-06-04
177HRRT321883 x 2 x .188w HR TubeBrunswick$ 8.68228392021-05-26
178HRRT321883 x 2 x .188w HR TubeBrunswick$ 8.09226572021-05-06
179HRRT321883 x 2 x .188w HR TubeBrunswick$ 7.87223402021-04-15
180HRRT321883 x 2 x .188w HR TubeBrunswick$ 7.51220712021-03-26
181HRRT321883 x 2 x .188w HR TubeBrunswick$ 7.34219892021-03-23
182HRRT321883 x 2 x .188w HR TubeBrunswick$ 7.34219602021-03-18
183HRRT321883 x 2 x .188w HR TubeRussel$ 6.51215912021-02-23
184HRRT321883 x 2 x .188w HR TubeMcNeilus$ 6.52214032021-02-09
185HRRT321883 x 2 x .188w HR TubeRussel$ 5.90212312021-01-27
186HRRT321883 x 2 x .188w HR TubeMarmon Keystone$ 6.88212122021-01-26
187HRRT321883 x 2 x .188w HR TubeBrunswick$ 4.90209412021-01-05
188HRRT321883 x 2 x .188w HR TubeBrunswick$ 4.90208422020-12-21
189HRRT321883 x 2 x .188w HR TubeBrunswick$ 4.04203612020-11-10
190HRRT321883 x 2 x .188w HR TubeBrunswick$ 3.83201492020-10-26
191HRRT321883 x 2 x .188w HR TubeBrunswick$ 3.83201072020-10-22
192HRRT321883 x 2 x .188w HR TubeMcNeilus$ 3.83200362020-10-15
193HRRT321883 x 2 x .188w HR TubeBrunswick$ 3.37199702020-10-09
194HRRT321883 x 2 x .188w HR TubeBrunswick$ 3.37188242020-07-02
195HRRT321883 x 2 x .188w HR TubeBrunswick$ 3.40188042020-06-30
196HRRT321883 x 2 x .188w HR TubeBrunswick$ 3.52176992020-04-02
197HRRT321883 x 2 x .188w HR TubeMarmon Keystone$ 3.76175352020-03-13
198HRRT321883 x 2 x .188w HR TubeRussel$ 3.88158202019-07-23
Pricing
Cell Formulas
RangeFormula
D34D34=1.236*1.25
D38D38=1.25*1.8
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

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