Solved

Marco Excel 2007

Posted on 2012-03-12
6
169 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

777 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