I am trying to have a list box that when clicked will change the chart data to whatever is selected. There are 2 options you can choose from. Order Cost (Net) and Order Total (Gross) The problem I'm running into is that the data is in 2 different rows. How can I create the drop down box to display the one that is selected? ...
View 9 RepliesI 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.
I am aware of the Visio capabilities of importing data into the org chart wizard to create an organization chart. Is there a similar capability in Excel only? I know there are smart art org diagrams, but if I have a spreadsheet of several hundred people, is there a way to import it into a chart (again, similar to the Visio process).
View 3 Replies View RelatedTrying to create a chart from a table of data, however my column of data has zeros throughout. How do I insert a chart with just the data that are not zeros?
View 1 Replies View RelatedI want to create the chart below. I have data that is coded with a binary flag (0 = red, 1 = blue) that is also ordered. For example, the data I would use to hypothetically create the left hand column below looks like this
Binary Flag
Rank
0
1
0
2
1
3
0
4
1
5
1
6
Here is my scenario, I have the following data:
Data Series 1
a=3
b=3
c=4
Tot 10
Data Series 2
d=2
e=2
f=2
g=1
h=1
i=1
j=1
Total 10
Data series 3
k=4 (d+e)
l=3 (f+g)
m=2 (h+i)
n=1 (j)
I want the three stack bars together, for the series 2 & 3 I want them without any gap so I can see how data series 3 is created.
I can't find a way to create a three stack bars chart that don't have the same number of data series, I don't want percentages.
I Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly
View 10 Replies View RelatedI've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.
list-test.xlsx‎
I am looking to create a number of series on a chart (xyscattersmooth) from a datatable. Below is my data Table....
View 9 Replies View RelatedI need drop-down combo box to change a graph based on the option selected in the drop-down
Challenge: my Combo box resides on the First sheet only, but the items that populate in the drop-down list are dynamic and change depending on how many sheets exist for this document. The options in the list are Overall, Week 1, Week 2, ... Week N. Overall needs to sum up the data (or I can pull just from the summary page if I am lazy) and the Week N corresponds to a specific sheet added.
I currently am able to get my drop down to populate items correctly, and my macros to create the sheets dynamically with templates just fine (not bad for newbie).
So what I need to figure out is how to tie my drop down to make a scatter plot/bar graph based on the selected option and action. I can't find an attachment option here but here is some code:
Sheet 1 Code:
Code:
Private Sub Worksheet_Activate()
Me.ComboBox1.Clear
Me.ComboBox1.AddItem "Overall"
[Code].....
I have a leave history report with data for leave taken for the 2014 year. The sheet contains the following headings:
Name; Leave Start Date; Leave End Date; Date Paid; Days Taken; Roundup; Offset
The roundup column rounds the days taken up, for example if someone took 0.4 days off it would round it to 1.The offset column just subtracts the Roundup value by 1 (=Roundup-1) I think we will need this for what I am trying to do.
I have created a new spreadsheet labelled gaant chart, this shows the name of the employee with the working dates for the year and has the following data:
Name
1/01/2014
2/01/2014
3/01/2014
6/01/2014
7/01/2014
8/01/2014
9/01/2014
10/01/2014
[code].....
What I would like for the macro to do is lookup Employee 1 from my gaant chart table and search the leave history data if it finds a relevant start date for the employee it marks that date with a 1, it then looks in the offset column and marks the number of offsets to the right. For example if employee 2 took leave from 1/1/14 to 3/1/14 the macro would mark a 1 on the 1/1/14, and search the offset column which would have 2, it will then mark a 1 in the 2 cells to the right of 1/1/14 also (these being 2/1/14 and 3/1/14)
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.
Sub ConsDiscChart()
ActiveCell.Offset(29, 11).Range("A1").Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(0, -1).Range("A1:C24").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
I am trying to create a data validation list with a bar graph. I have tried using the IF and Vlookup formula, but its not working for me. I would like to be able to select classroom # from the drop down menu and the bar graph update selected with student’s name and selected data information. I have attached the file.
View 5 Replies View RelatedI'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
I want to draw Excel chart Depending On chart Type I Filter From Data List In Cell H4 I & Change The Excel Chart Title Automatically To The Name Of Chart Type I Filter On
Sheet1
ABCDEFGHIJKL1TypeDatesValues 2Monthly01-01-2012319 3Monthly01-02-2012176 Chart 4Monthly01-03-2012356 Monthly 5Monthly01-04-2012317 6Monthly01-05-2012436 7Monthly01-06-2012461 8Monthly01-07-2012323 9Monthly01-08-2012417 10Weekly01-01-2012461 11Weekly08-01-2012279 12Weekly15-01-2012244 13Weekly22-01-2012190 14Weekly29-01-2012117 15Weekly05-02-2012202 16Weekly12-02-2012146 17Weekly19-02-2012422 18Daily01-01-2012195 19Daily02-01-2012354 20Daily03-01-2012357 21Daily04-01-2012354 22Daily05-01-2012289 23Daily06-01-2012205 24Daily07-01-2012371 25Daily08-01-2012304 26Daily09-01-2012496 27Daily10-01-2012307
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
I would like to find out how can I create a chart, where it is interacting with the selection of 3 drop down list (created from data validation-list option). It will show up to 12 months of data.
First list would have only Male & Female, 2nd list would have 1st Class, 2nd Upper, 2nd Lower, 3rd Class and Pass Only, 3rd list would have all the months of 3 years.
My data is from J9:U22. These data needs to be changed accordingly, reading from D45:U58.
For the graph, if I select Male, 2nd Upper, and Jun-2012, I want it to show only this selection, where the period will be from Jul-2011 till Jun-2012.
Previously I have done one, with only 1 selection of drop down list, where I used Vlookup. Now involves 3 criterias.
Or is there an easier way to do it?
I have some raw data of employment records on one workbook and from this i need to autopopulate there information into another workbook.
I need to split the records up by the different departments they work . I.e so what i am after if possible is -
If The department name in the raw data matches column A1 for example then it auto populates down in B2 all the names of people who work in that department .
I have a client list/table that gets new data each day. (ALL) - see file
I need to create separate lists/tables on new sheets from the main data sheet (ALL) base on certain conditions/criteria...
On the (ID) sheet I need all the clients that have ID numbers to display, on the (Reg no) sheet I need all the clients with Reg numbers to display... etc.
Like I said that the main data sheet grows each day and the lists/tables on the other sheets needs to automatically include all the new clients that's entered.
Is there a way to do this using formulas or do I need code to do this?
I have data contained in matching cells from several sheets. I need a worksheet that reflect the summary of the data from the worksheets. No formula is required. I just want my summary worksheet to list the all the data from my other worksheets. I might end up having about a hundred worksheets that I need included in the summary. Attached is a sample. It would be better if the summary automaticaaly updates as new worksheets are added.
View 3 Replies View RelatedI am in the process of creating a database, and have used 'Data Validation' to create drop down lists in order to obtain information quickly from the database.
I have encountered an issue where if I have a list the resembles the one shown below, the drop down list will show spaces.
This is particularly an issue for myself and my database because there is over 1,000 items in the list, and there are countless empty cells within the list. I have tried sorting the list, BUT because the database is quite large, sorting it will disrupt other functions of the database.
Ideally, I would like the drop down list to show only entries within the list that are not empty so the drop down list is entirely relevant, and more user friendly.
I can't seem to find the answer to this question. I have a sorted list of data 3 cells wide, 120 cells long. I wish to have this data put into a scroll bar so I may scroll the data, select, and it will output to a chosen cell.
I assume the ScrollBar control is the choice but since I have no experience with any controls, I am at a loss. I have thus far only managed to insert the scrollbar, click on the properties and then I'm ?.
1. How do I specify the range of cells to display?
2. How do I specify an output cell after selecting from the list?
I've attached a spreadsheet to view the data.
I have a workbook which contains a list of data on the first worksheet. I need for a macro to copy the name in each row,along with its data in that row and paste it into the corresponding locations on a template located on another worksheet. It would then save the file using the date (col. a) , then the words " Scorecard for" and then the name (col. b), then loop for each row until it reaches the end of the list.
I located this code here here.However I dont know how to modify it to fit my needs. I dont have 2 separate categories for two score cards in column b. It would all run off one scorecard and each row of data.
[Code] .....
I am trying to create a drop down list so that when the user choose Option A, Outcome A populates the cell.
So for example, I am looking to show a list of all Grades within the company, when the user selects a grade, the salary is populated into the cell.
I have a spreadsheet called contacts which is as it says, I have another sheet called project management, I want to have a drop down list which refers back to the contacts spreadsheet, it this possible as they are two different files?
View 4 Replies View RelatedI 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 will copy a report into sheet 1, starting in the same place(A1), on a weekly basis. The headers are: Item, Quantity & Price
Week 1's report shows the following:
Windows 5 $1.00
Doors 6 $1.50
Chairs 7 $2.00
Week 2's report shows the following:
Windows 3 $1.00 (change in quantity)
Tables 7 $5.00 (new item)
Chairs 7 $2.50 (change in price)
I need to have a rolling, unique list on Sheet 2 that will do the following:
- add new line items
- keep old line items while checking to see if the quantity or price has changed.
Therefore, after week 2's report is pasted on sheet 1, Sheet 2 should look like this:
Windows 3 $1.00
Doors 6 $1.50
Chairs 7 $2.50
Tables 7 $5.00
Here's the most important part: There will be formulas in Column D in Sheet 2 that I need to stay with each item. generating the desired result on Sheet 2!
I need a Macro to create a unique, rolling data list. On Sheet 1, a report is pasted(from outside source) in cell A3(Rows 1 & 2 are headers) on a weekly basis. It is always the same amount of columns wide(138). The number of rows will always vary but will never be more than 500.
Here’s what I need for Sheet 2:
-Column A should be a copy from Column A on Sheet 1 (data is ITEMS)
-Column B should be a copy from Column BH on Sheet 1 (data is QUANTITY of items)
-Column C should be a copy from Column BI on Sheet 1 (data is PRICE of items)
Here’s the challenge (when the new report is pasted on Sheet 1):
-Sheet 1. Find new ITEMS not on Sheet 2 and paste them at the end of the report on Sheet 2 along with their QUANTITY and PRICE
-Sheet 1. Find any ITEMS already on Sheet 2, then update new QUANTITY and PRICE from Sheet 1 onto Sheet 2
-Sheet 2. Leave ITEMS alone that are now missing from the new report on Sheet 1
I have a spreadsheet of 12 columns (A to L), with a list of student names under each down to row 31. Each column represents an after-school club that the child can attend.
However, a child can sign up to multiple clubs, so their name can appear in several places across the spreadsheet.
What I would like to do is have a 13th column which is an overall list of students who have signed up to at least 1 club, i.e. their name appears at least once in columns A to L.
How do I achieve this so that only unique entries are listed and duplicates are removed in this 13th column?
I am using following code to apply data validation list. However this code gives error Intermittently. The error message is 'Application defined or Object defined error'. Also this code looks little cumber some. Will really apprciate if any one can put some ligh on why is this error appearing sometimes. Do I need to change something in the code or add few extra lines. I feel following points will be necessary when you check my code
1. The data of validation list is stored under the column heading 'Reported_By_List'
2. Validation is applied on the range under the column heading 'Reported Through'
3. Start and last Cell Address Of the cells of the column in which data of validation list is
stored are extracted into the variables 'StartCellAddress1' & 'LastCellAddress1 '
4. Start and last Cell Address Of the cells of the column on which data validation will be
applied are stored are extracted into the variables 'StartCellAddress2' &
'LastCellAddres2'
Sub Validation1()
Dim wsName As String
Dim RValue As String
Dim WorkBookName As String
Dim StartRow As Integer
Dim StartColumn As Integer
Dim LastRow As Integer
Dim CellAddress As String
Dim ColumnAddress As String
Application. ScreenUpdating = False
WorkBookName = ThisWorkbook.Name
wsName = ActiveSheet.Name...........