Solved

Marco Excel 2007

Posted on 2012-03-12
6
188 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
[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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

691 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