Excel Dynamic Chart #15: Array Formula & Table Nomenclature for Top 10 Wrestling Score Chart

Hello! Just wondering if anyone can help me.

My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way.

So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well.

This solution also worked perfectly, but now I've been told to create the graph without macros.

Does anyone have any suggestions? The requirements/details are as follows:

1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH"
2. The Pivot Table has two criteria - School Name and Year Level
3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels

Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?

Thanks!

Frustrated Tim!

Hi all,

So recently i attempted to make interactive chart. My example is the S&P/TSX volume and date. The idea is i can choose a start date and a preiod of time, then the chart will be updated. (see Screenshot)



The Name of the array to update the chart is "ShowPrice" and "Month". I want to display a table that print out the values of the two array right beneath the chart (i will move the raw data on the left of the chart to a separate sheet). I have an general idea of displaying the values corresponding to the chart using
Code:

 = VALUE(INDEX(ShowPrice, 1)) 
and = VALUE(INDEX(Month, 1))

and drag it out for the entire row. However, i just wonder is there is a better way to print out the table that display values corresponding to the chart as the chart updated (maybe using array formula). I am hoping for a way to do it with just excel formula, but open to macro as well.

Thank you.

Hi All,

I've found great explanations regarding Dynamic Charts (e.g. http://www.contextures.com/xlNames02.html or http://peltiertech.com/Excel/Charts/...hartLinks.html) however all examples refer to data within a table than extends vertically.

I however would like to creat a dynamic chart on a table with data going horizontally. I assume you have to alter the OFFSET formula to Count for the data going horizontally, but not sure how to do this?? For example, please refer to attached Chart sample in 'Sheet 1'.

Does anyone know how to kick start me on this??

Is it also possible to automatically collapse the x range on graph (e.g. data in attached only appears till July, so would like to only show chart till July, rather than Dec. However august data is entered, the chart would extend to show aug)

Regards
Tim

Howdy All!

I've been working on a project in Access. It works fine, and exports tables to Excel. The number of records varies each time the Access file exports. Therefore, I've created a chart w/ dynamic ranges.

The chart works fine....It grows and shrinks accordingly. I'd like to include a line to show the average...dynamically. I found an equation that works w/ dynamic ranges. This function works well (found in cell C2).

However, I can't get that info turned into a line on my column chart. When I add that value into the chart, and change it to a line chart, I end up w/ one single data point. Understandably, because that's all there is.

Therein lies the question: How do I apply that value dynamically for each record so it shows as a line on my chart?

I've tried using trend lines, but they don't actually average...so they're not very helpful.

Attached is an example. Feel free to play w/ the data (add or remove as you will). The column chart changes accordingly, as does the average value in C2. But...I'd still like to get the average value to show as a line...

Thanks for the help.

Hello
I have a spreadsheet with dates in colum A and numbers in columns B to D. The dates span a full year and I have charted those dates and their values on a line chart. I want to be able to provide two drop downs or a free text field where the user can select two months they want to display in the chart and this will update the chart data accordingly.

Is this possible without using pivot tables?

Thanks

I have one table based on pivot table. and it is used for the dynamic chart. so the table is a dynamic source for the chart. Now I want when the pivot table updates the table should Also update. I have tried to using formula referenced to the pivot table it works but as the chart is dynamic it takes the all cells containing the formula though they are empty. I have attached a sample sheet for perfect explanation of the problem.

Thanks.

how do you create pivot table for each of the company's share and another pivot table that combines all the companies' share? the data should be converted to percentage in the tables.

how to make the pivot table update whenever there is new data? and the chart should update too, be it auto or manual.

how to make the pivot table check for null first, before showing the percentages? cause the new data might have 'NaN', which means there is no closing price for that particular day. how to make the pivot table so that it will ignore the 'NaN', but not delete it?

some sample data (they are stored in different worksheets):
these are the closing prices of company 1(Asia, sector 1)'s share.
Date Close Wednesday, January 02, 2008 56.32 Thursday, January 03, 2008 57.65 Friday, January 04, 2008 57.2 Monday, January 07, 2008 58.4 Tuesday, January 08, 2008 58.9 Wednesday, January 09, 2008 58.74 Thursday, January 10, 2008 57.75 Friday, January 11, 2008 57.03 Monday, January 14, 2008 57.33 Tuesday, January 15, 2008 55.74 Wednesday, January 16, 2008 54.49 Thursday, January 17, 2008 53.46 Friday, January 18, 2008 53.25 these are sample data for company 2(Asia, sector 2)'s closing share prices.
Wednesday, January 02, 2008 14.24 Thursday, January 03, 2008 14.36 Friday, January 04, 2008 14.06 Monday, January 07, 2008 13.92 Tuesday, January 08, 2008 13.94 Wednesday, January 09, 2008 13.79 Thursday, January 10, 2008 13.39 Friday, January 11, 2008 13.43 Monday, January 14, 2008 13.43 Tuesday, January 15, 2008 13.01 Wednesday, January 16, 2008 12.71 Thursday, January 17, 2008 12.55 Friday, January 18, 2008 12.3 these are sample data for company 3(Non-Asia, sector 1)'s closing share prices.
Tuesday, January 01, 2008 247.9 Wednesday, January 02, 2008 243.3 Thursday, January 03, 2008 254.85 Friday, January 04, 2008 261.25 Monday, January 07, 2008 260.85 Tuesday, January 08, 2008 251.45 Wednesday, January 09, 2008 252.8 Thursday, January 10, 2008 241.8 Friday, January 11, 2008 239.2 Monday, January 14, 2008 241.05 Tuesday, January 15, 2008 240.2 Wednesday, January 16, 2008 231.65 Thursday, January 17, 2008 234.7 Friday, January 18, 2008 222.8 these are sample data for company 4(Non-Asia, sector 2)'s closing share prices. Tuesday, January 01, 2008 4950.5 Wednesday, January 02, 2008 5073.8 Thursday, January 03, 2008 5093.75 Friday, January 04, 2008 5379.4 Monday, January 07, 2008 5295.95 Tuesday, January 08, 2008 5151.45 Wednesday, January 09, 2008 5058.5 Thursday, January 10, 2008 4927.35 Friday, January 11, 2008 5055.7 Monday, January 14, 2008 5017.65 Tuesday, January 15, 2008 4902.65 Wednesday, January 16, 2008 4792.95 Thursday, January 17, 2008 4656.45 Friday, January 18, 2008 4382.3 end-result of the pivot table (all 4 companies combined):
this pivot table should allow data analysis/filter based on
i. by year: All, 2009, 2008,....
ii. by market sector: All, Asia, Non-Asia,....
iii. by industry sector: All, sector 1, sector 2, ...
iv. by month: All, Jan,...., Dec


then from this pivot table, how to create pivot chart?
end-result of the pivot chart:



please help!
thank you very very much!

I use pivot table and X-Y Scatter plot for analysis of data. Is it possible for scatter plot to select the chart series automatically, as I select the different pivot table fields?

I use x values as the 'Row Field', and Y values with 'Data Items'. And 'Column Field' as a different series. (Please check first figure [pivot.jpg])

Is it possible to write a macro which automatically plots
column field - as Series Name
Row field - as X values
data items - as Y values

(I have figured out how to plot X-Y Scatter plot for Pivot table, which is generally not allowed.)

Consequently, Is it possible to do that same if there are more than one column fields? (Check Figure 2 [pivot2.jpg])

I have worked with excel macros in the past. But for this particular problem, I am not able to come up with any logic so far. So, please help.

I hope that I have explained the problem clearly. Thanks.

I have designed a couple of graphs using named ranges to give dynamics to the number of series in a chart. This works fine as long as I don't want to copy and/or move the sheet to a new work book. When I do this, Excel chooses to remove all references to the named ranges in the SERIES forumla and replace them with fixed references (despite having scoped all named ranges to WS scope).

From googling I found the tip to save the WB as a template and use Insert sheet to add the graphs. This does not quite work as expected though. The SERIES formula points to the right named ranges, but when I update data the graph is not updated. If I save the new workbook and reopen it, it works as expected. Other quirks include not being able to Insert a second template without saving and reopening (the named ranges points to wrong sheet if I do not do this).

So my question is two-fold; has anyone seen this problem before and solved it? Or, has anyone been able to successfully copy/move a sheet and keep the named ranges without involving a "relink" macro?