Solved

Marco Excel 2007

Posted on 2012-03-12
6
160 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

867 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

23 Experts available now in Live!

Get 1:1 Help Now