[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Make a Dynamic Chart w/ Drop Down

Posted on 2011-09-21
10
Medium Priority
?
303 Views
Last Modified: 2012-05-12
I want to make a chart with a drop down at the top for "category" and when you select a catergory it only shows those locations and points on the graph.

I have the following fields:
Category
Location
Data1
Data2
Data3

I want the category to include a drop down that when you select the category the graph changes accordingly. Also, there should be a catergory for ALL.

I have done this many years ago using the OFFSET function however I cannot fully remember how to do it.

PLEASE HELP!!!
0
Comment
Question by:kwarden13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 50
ID: 36576958
Hello,

how many rows with the same category will the table have? This approach is for unique categories:

In the attached sample, the data source is defined with an Offset function and a nested Match that finds the correct row.

cheers, teylyn
Book2.xlsx
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36576972
You can do this with a pivot chart.  It will automatically give you the drop downs to select category if you make that a page field.  It's difficult to show you an example without seeing your data.   If you're prepared to post some sample data it would be easier.
0
 
LVL 2

Expert Comment

by:jan24
ID: 36577078
I would agree with andrewssd3 that PivotCharts are a powerful solution for this kind of problem.  Attached is an attempt using Excel 2007.  If you need an Excel 2003 version then let me know.

Obviously the downside of the PivotChart is that the user interface is less customisable.
Book1.xlsx
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:kwarden13
ID: 36577118
I dont want a pivot
0
 
LVL 50
ID: 36577149
kwarden,

if my first suggestion and my guess at your data does not work for you, it would be good to see a data sample. Dynamic charting relies heavily on data layout and the dynamic range names must be properly defined.

The above is only one of many ways how it can be done. If your data is different, the approach will vary.

cheers,
0
 
LVL 2

Expert Comment

by:jan24
ID: 36577476
teylyn's dynamic range names don't work for me - Excel 2007 chart seems to get upset by them (I remember this working in 2003, so perhaps I'm missing quirk of 2007).
Anyway I tried a different approach which does not use PivotTables and is a bit less elegant than dynamic range names ... but it does seem to work - see attached.  
As teylyn says, data structure is critical.  Here I've assumed that all the rows for each category come in a block.
Book6.xlsm
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36577771
jan24,

To use a dynamic named range, you have to set the series value or labels like this:

='Name of file.xlsx'!Named_Range

or

='Name of sheet'!Named_Range

Do that, and it will work splendidly.  I do have a fondness for PivotCharts, though :)

Patrick
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 36577827
Kwarden,

here is a VBA-free  approach with dynamic ranges for a data set with multiple occurrences of the category. With these formulas, the data should be sorted ascending by category.

The range names used are

CategoryBase      =INDEX(Sheet1!$A:$A,MATCH(Sheet1!$I$1,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(Sheet1!$I$1,Sheet1!$A:$A,1))
chtData1      =OFFSET(CategoryBase,0,2)
chtData2      =OFFSET(CategoryBase,0,3)
chtData3      =OFFSET(CategoryBase,0,4)
chtLabels      =OFFSET(CategoryBase,0,1)

See attached. You can move the chart to a chart sheet or to a different sheet, if you want to.

cheers, teylyn

Book5.xlsx
0
 

Author Comment

by:kwarden13
ID: 36581105
Attached is a data sample

teylyn: I like your method or something similiar, however can't get any of it to work. Can you save in a 2007 format?
datatest.xls
0
 
LVL 50
ID: 36583426
Hi, as I said above, the data must be sorted by category for this approach to work. If that is not an option, the you will need to use pivot tables. There is no formulaic way to create a dynamic range for data that is not contiguous.

I've saved the above file in Excel 2003 format.

When you apply the dynamic range names to the chart, you must enter them with the syntax

=SheetName!RangeName

or

=FileName.xls!RangeName

Entering just the range name will not be accepted.

cheers,

Book5.xls
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question