I have a table of data which moves up and down my report depending on user input changes to the source data.
I have written some VBA code to generate a chart based on this table of data. I have managed to create a basic chart from this table. However I need to change the series (on the chart) from rows to columns so that it is displayed correctly. For this I need to have the cell references within the code belonging to the actual chart.
If I was writing the code for the chart with an absolute reference I would write it as:
I want to write a macro to add a surface chart for a Data Range which includes 6000 data points. But the series selected for the chart don't cover all Data Ranges, only part of them.
Public Sub AddChart2(LastRow As Integer, LastCol As Integer)
Dim cht As ChartObject, currentSheet As Worksheet Dim rng As Range, newRow As Range, srcRange As Range Dim colIdx As Integer
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
Trying to create an excel chart to create totals based upon different keys. I need to be able to calculate how many customers there are by Manager and then By Rep. Then to figure out how many were New, Current, Total # of RSVP and attended for that Rep. Below is how I have started but I am having some problems getting certain parts. I know when I get one the rest will fall into place. I can calculate how many total customers by manager and by rep just by doing a Countif command but how do I determine the # of New, Current etc. Is there a If Than command? Managers Totals are simply his reps totals.
Example.. A B C D E F G 1 Manager Rep Customer New Biz Current # RSVP # Actual Attend
Need totals to look something like this....
A B C D E F G 1 # of Cust # New # Current #RSVP # Actual Attend 2 Manager 3 Rep 1 4 Rep 2
Sheet1 ABCDEFGH2Days12345673Values2040550000 Excel tables to the web >> Excel Jeanie HTML 4
I have plotted a line graph based on the above table; but I am getting zero values also plotted. since these Values are linked to some other workbook I can't delete the cell values also. How can we avoid Zero values on a graph; Also is there any approach, that my graph should get automatically updated whenever the linked values are updated.
The user will select a range (example, B4-Z4). The macro needs to test each cell to see if the number is in a certain range (example, is the number in the cell between 21-40, 41-60, 61-80, etc? - these ranges will not always be the same on each worksheet). If the cell is in that certain range, that cell is a 1 for that range. Example,
B4 = 23 21-40 = 1
C4 = 30 21-40 = 2
D4 = 45 41-60 = 1
After all ranges are tested, it will be graphed on a separate worksheet with the x-axis being the ranges (21-40, 41-60) and the y-axis will be the total number of cells that fit in the range. The above would be....................
I am trying to create 2 drop-down lists (based on named ranges), where the contents of the second selection change based on the first selection. On one sheet, I have a list of equipment that is quite long, but is broken up by categories. I have named ranges for all the category names, and the equipment in each category. On the other sheet (user side) I made the first drop down the list of categories, and would like the 2nd drop down to be the pieces of equipment that correspond with the chosen category. I have tried using data validation and nesting IF formulas, but it only works for the first 4-5 categories, bc my formula is longer than the Source field allows in Data Validation. Any other way I can make this work? Can I use a macro for this? If so, how to write it as this would be my first macro
I have an XY chart that I would like to have the user select a single data point in the chart and be able to add a comment in a table corresponding to that point. For example: if the x-values are in A1:A10, y-values are in B1:B10, the user can select a point, say the 4th point, have some code run that will enter a text string into cell C4. I can manage the code to add the text to the appropriate location, but I'm having trouble with 2 things.
First, how can I ensure that the user has selected a single point (as opposed to the entire series or the chart background)?
And second, how can I determine which point is selected? I think I need to determine the point's item number in the SeriesCollection, then offset that number of rows in my data table. But I can't find a way to retrieve that number.
I am trying to create a drop down box that pulls its selection from another sheet in the workbook and I'm not sure how to go about doing it. I figure VBA must have some way of doing it but I am not sure how the coding would go. I've attached a very basic example of what I am trying to accomplish.
I am creating a yearly rota that will produce a signing in sheet on a day and night basis. Im using a userform to pick the... Department, Operators name, Time In & Time Out.
The Problem I have is that the ComboBox in my UserForm for selecting the operators name shows every operator in the rota, where as I would like the Combobox in my user form to only show the operators that are on shift that day/night. To reduce the chance of selecting an operator that isnt even on shift that day.
I would also like it to subtract the names after the have been selected. to reduce the chance of entering the same name twice in the same sheet.
I have a large set of data that I need to use for data validation in drop down lists. The data is arranged in 4 columns and entries within each column may repeat numerous times. The four columns are as this..
Category Subcategory 1 Subcategory2 Subcategory3
The first data validation will come from the first column. Once this is chosen the second drop down should populate with the subcategory that correspond to that category stored in column 2. once that is chosen the thrid data validation should populate with the corresponding subcategory from column three etc.. This is a small sample of the data in the columns.
I am working on a spreadsheet for a client. I am using Excel 2007 and she is using Excel 2000. I have created a emplate for her to enter survey data into. Using the following code, I have created combo boxes for the drop-down menus so the client can tab from one cell to the next while entering data:
Option Explicit Private Sub TempCombo_KeyDown(ByVal _ KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) 'Hide combo box and move to next cell on Enter and Tab Select Case KeyCode Case 9 ActiveCell.Offset(0, 1).Activate Case 13 ActiveCell.Offset(1, 0).Activate Case Else 'do nothing End Select End Sub.....................
I am trying to create a pivot chart that show the average response times in hour but for some reason my charts looks not right. The axis show a max of 1 but the graph itself showed us up to 17. I think easier to explain if I attached the worksheet.
I've used trend lines to find the mean and average but for some reason I have not been able to set an upper and lower limit. without introducing a new column.
I have two items: Written Premium and Earned Premium. Each of these items has a forecast and actual number for two months. How can I create a line chart (with dots) that will show both of these items. I've attached a spreadsheet.
Create a well labelled pie chart on a separate worksheet to show the profit made by each of the Liverpool departments to the overall profit for 2006. On the labels show the percentage contributed by each department.
I want to create a pie chart in Excel 2007. The data is not next to each other in the columns. How do I create a chart so that it includes the legends as the various departments and the data shown as the figures given under 2006?
I'm trying to loop through a worksheet where each row is an individual record. I need a macro to read the row and create a chart on a separate worksheet for that row, then move on until a chart has been created for each individual row. In English the problem is:
Read Row 1 Create chart based on row 1 data in new worksheet 1 Read Row 2 Create chart based on row 2 data in new worksheet 2 And so on
I'm making a risk chart which i want to select data automaticly.
It currently is possible to select a characteristic. After selecting the characteristic i want the chart to update, having only the risks with that characteristic in it. But it also has to select on impact level and chance%.
Is there any way to let Excel auto select the Risk discription and copy it into the chart?
below are 2 pictures, 1 an example database of risks and an example of how the chart should, more or less, look like when i select the financial characteristic.
I am trying to create a horizontal bar chart with a product code data set. The number of codes will increase over time. For each product code there may it may not be a sales value and that sales value will increase over time. I am trying to plot the product codes on the y axis and sales value in the x axis.
I want to make a bar chart, When i use Excel and try to make it - i get a chart which shows the right column height but the Male and Female bars are BOTH IN 1 Colour. How do i make it so that male bar is blue and female is pink.
