Solved

Marco Excel 2007

Posted on 2012-03-12
6
151 Views
Last Modified: 2012-03-14
Hello,

I have a table in excel with 135 rows (134 customers + header) and 13 columns (Jan to December + customer name) capturing 12 month of sales for each customer.

excel macro to produce linear chart
I'm trying to produce a macro that when I press a button it ask me which customer of my list of customers captured in my range B3:B134 I wan to produce a linear graph for. If, for instance I chose customer ABC Ltd, which is in in row 3, it then captures the range B2:N2 (the header showing the months) and then B3:N3, which has the monthly sales figures.

Hope this all make sense?

Thanks,

Fernando
0
Comment
Question by:Fernandino1977
  • 4
  • 2
6 Comments
 
LVL 9

Expert Comment

by:armchair_scouse
ID: 37710277
I have a feeling you might need to use VBA to achieve this, though I am prepared to be proven wrong! :o)

You need two elements to fulfil your requirements:
a) the prompt to ask you for which client you wish to produce a linear graph
b) the graph itself

Here's an idea of how you could approach this:
- create your linear chart manually for one customer by doing this:
  - add a new worksheet to your workbook with the table in it
  - copy your data series (months and sales figures) to a defined data area on your chart
     worksheet (e.g. in columns A and B)
  - use the Chart Wizard to build your linear chart (as an example)

Once you have the sample chart, use some VBA code to do the following:
 - prompt for a company name (the InputBox method should do http://msdn.microsoft.com/en-us/library/aa195768(v=office.11).aspx)
 - once the value from the InputBox has been checked against the list of values on your spreadsheet, if a value is found, copy the appropriate data series to your chart template worksheet in the defined data area
 - refresh/update the linear chart (and change the chart title to match the selected company)

If you are unfamiliar with VBA, then say so, and I imagine further examples may appear here... ;o)
0
 
LVL 9

Expert Comment

by:armchair_scouse
ID: 37711151
I have attached a sample file to illustrate what I said above.  It'll give you a head start.
Chart.xlsm
0
 

Author Comment

by:Fernandino1977
ID: 37714887
WOW, that's awesome! Thanks!

Is it easy enough to create a drop down menu that allow you to select the company instead of typing it down? I have over 130 customers and that will make life so much easier :)

Fernando
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

by:armchair_scouse
ID: 37719595
To create a dropdown menu, you'd need to create a UserForm and add a dropdown component to it, and add an OK button, then display the UserForm instead of using the InputBox.  You can populate the the dropdown using an Excel range, using this kind of syntax:

cboCustomer.List = Worksheets("Data").Range("A2:A100").Value

Then once you show the UserForm, the user selects a customer then clicks OK.  You can read the selected value in cboCustomer and then continue the processing from there.
0
 
LVL 9

Accepted Solution

by:
armchair_scouse earned 500 total points
ID: 37720202
Here's an updated version with a dropdown box on a userform.  Tweak as you see fit.
ChartPlusDropDown.xlsm
0
 

Author Closing Comment

by:Fernandino1977
ID: 37720573
Your solutions absolutely rocks!

Thanks a lot. I've copied paste the code in my excel sheet and adjusted the columns and range and it's working like a treat.

Thank you so much!

Fernando

Ps.- If I get the time I'll get to learn VBA. So powerful if you know it well :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now