Building A Chart
Oct 8, 2007
I am building a chart from a pivot table and the data changes weekly therefore my chart changes weekly and I want to have the same colors on my graphs from week to week. I created this code to keep everything the same. Problem is when some of the data is not there for a given week (say no calls from a certain city) a corresponding data set doesn’t get created and the macro won’t work. Is there a way to run if the a data set is not available and to keep the colors the same?
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With..............
View 9 Replies
ADVERTISEMENT
May 14, 2014
I have a sheet with about 50-60 charts and I am hoping to have a master list of chart titles on the side, how can I link these titles with the charts themselves?
View 1 Replies
View Related
Nov 5, 2007
I need to update a Variable I called "Croy70" by making up its name from two cells
Left(Range("L" & i), 4) & Right(Range("J" & i), 2)) = i
Where i=2 and L2 is "Croydon" and J2 is "ABC70"
I apologise for this simple question, but I appear to have left my brain at home this morning and all the VBA help files are missing from the computer I'm using!
View 9 Replies
View Related
Nov 23, 2009
I can start building.
1. User will first input Medication type in first column. Second Column - Dosage Third Column - Start Date, Fourth Column - End date
There will be multiple entries for the same medication because of dosage change. I can simply get them to insert a new row underneath each medication, and they can then input new dosage, new start/end dates
There will be a cell entry for the form start date, as it will be populated on the main page.
The solution I believe will involve an if statement, with argument being if date is still within range of start/end date, that it will generate text on particular date.
View 9 Replies
View Related
May 10, 2006
I need to build a formula that looks up a vertical value and a horizontal value and retrieves the value where those two values meet
for example
REGION
Grade ST LOUIS MILWAUKEE SOUTH DAKOTA
C10 30,000 35000 45000
C11 27500 25000 30000
C12 22000 60000 23500
I need the formula to retrieve that value for grade C11 and in milwaukee which is 25,000
View 4 Replies
View Related
Sep 21, 2009
I have Sheet 1 with a series of 10 lists. Sheet 2 will display the information on Sheet 1 based on the day. For example, the user will input a start date. Excel should display item 1 in lists 1-10 on day 1 in the respective column. Then item 2 in lists 1-10 on day 2, etc. When it reaches the end of a list it should start at the beginning. Day 1 shold be the start date. Day 2 shold be the next day, etc. I have lists 1-10 all ready to go, but I am having trouble figuring out how to go about populating the other columns.
View 6 Replies
View Related
May 24, 2013
I am building a form with a list of names and check offs next to them. There will be a list of 150 names and 3 check boxes next to each name. I want to be able to create a running total of each check mark. So I have a name with a check mark that marks off SSN, Drivers Licence, drug test At the bottom I want it to give me the total names that have a SS Card, Total that have Drivers Licence and so on.
View 1 Replies
View Related
May 23, 2006
I have a workbook with 3 worksheets titled “Numbers”, “Letter” and “NumLtr DB”.
The “Numbers” worksheet is a dynamic listing of numbers in Column A, which also is a Dynamic Named Range using the formula: OFFSET(' Num'!$A$1,0,0, COUNTA('Num'!$A:$A),1)
The “Letters” worksheet is a dynamic listing of letters in Column A which also is a Dynamic Named Range using the formula: OFFSET('Ltr'!$A$1,0,0,COUNTA('Ltr'!$A:$A),1)
I need a macro to duplicate in column A, on the worksheet titled “NumLtr DB”, the first number down as many rows as are needed to equal the full count of letters, and then, in column B, list each letter in succession. This process needs to be repeated once for succeeding each number and then stop.
View 9 Replies
View Related
Sep 14, 2006
We have a number of dogs and information about their sire (male parent), dame (female parent) - and those dogs parents.
In essence something like this:
DOG1: DOGDAD, DOGMOM, DOGDAD's DAD, DOGDAD's MOM, DOGMOM's DAD, DOGMOM's MOM
So...I'm thinking 7 fields for each dog.
I started out with an Excel s/ sheet that looked like this:
Dog Sire Dame SireD SireM DameD DameM
DOG1 DOG2 DOG3 DOG4 DOG5 DOG6 DOG7
DOG8 DOG9 DOG10 DOG11 DOG12 DOG13 DOG14
DOG2 DOG15 DOG16 DOG17 DOG18 DOG19 DOG14
Now here comes the issue....what we are trying to do is to find and exclude any dog (in the first 'Dog' column that share ANY grandparent (SireD, SireM, DameD or DameM) with any other dog from the first column.
(As an example above - DOG8 and DOG2 share the same grandparent (DOG14) and needs to be excluded from the final list).
View 9 Replies
View Related
Mar 6, 2007
I want to build a cash register from excel.
I want to put many buttons, but lets say one on A1 that says $2 dollar item and name and lets say this $2 doillar item button is pushed(clicked on), it will add $2 to lets say the total say A15.
Now I can't even get a Macro to add $2 dollars to A15(or any macros to run at all, anybody have code I can copy, what I understand is you create macro name(give it letter, so yo can press letter and Ctrl to start) then start recording, then you do key strokes or mouse? Then press stop recording and you have macro?).
To get buttons to be viewed I have to go to Tools - Macros - script editor, and drag and drop(button on coding) or double click on button tool bar then File - View in Browser - . I get button but can't get it to do anything.
How do you put say A1 in code to A1=A1+5 or make a memory location out of A1. Now you can go to(while A1 is selected cell) "Insert" then "Name" then "Define" and give A1 a "Name". I can't get this name to work in coding(but I can get the name to work in cells like "Name" +5, will add cell 1 and 5, if you do it in different cell than A1).
View 5 Replies
View Related
Jan 10, 2014
I am new to pivot tables and charts and to build.... I heard that the best is to use pivot tables but no clue how to do it I have tried but no luck....
How can I see the following information in chart?
-Non con close out time by person- so who is failing to close out on time in this case is completion date
-Frequency of reports by area/ shift
-Number of reports by month, split into critical/major/minor
-Main reasons for reports so trend of type of record
View 3 Replies
View Related
Mar 19, 2012
Fairly simple question, but I don't remember syntax.
The simplist cell reference is =F27
If you want to make the column reference a variable and the row reference a variable (=ColumnVarable:RowVariable) What's the syntax?
View 3 Replies
View Related
Dec 15, 2009
I want to build a file(name) list of all files in a directory including the ones in all underlying subfolders on any level.
E.g. make at total list of all possible files (not folder-names) in the given directory.
I have a clue, but I just manage to list the files in the first subfolder level - not proceed "deeper" in the folder tree.
View 9 Replies
View Related
Jun 18, 2014
I am trying to create a Macro/VB code that will build 4 lists based on a list (see attached example).
I have a list of applications and the numbers of users having them installed on their computers, these applications are marked using a RAG status (Red, Amber, Green & NBR No Business Requirement) on a seperate work sheet i want to split them by these RAG statuses, the status of applications changes as they are packaged for SCCM, so the RAG status will be changed, so i want the code to be able to rebuild the lists at any time to obtain an accurate view of the numbers at each status.
View 6 Replies
View Related
Mar 25, 2009
I'm trying to figure out how best to build a table to do:
IF A1="yes", "no", "na" and
IF B1="yes", "no","na" and
IF C1="yes","no","na" then
"AT" or "IC" or "VE"
So, I need a table that can look up responses to three questions, then lookup the corresponding answer (AT, IC or VE). Some of the combinations of anwers will yield the same anwer (i.e: yes, yes, yes=AT and yes,yes,no=also AT). I'm trying to come up with the most probable answer by evaluating these three questions; Thus; 10 combinations will yield an answer of "AT", 10 combo will yield "IC" and 7 combo will yield "VE". I've done this with 2 variables, but can't seem to get it to work with three...
View 4 Replies
View Related
Feb 25, 2013
I have data showing when staff arrive at, enter after clearing security and then exit a building after completing their task. The data for each cell is a combined date and time as opposed to separate cells for date and time.
I am trying to ascertain how long our security staff take to clear persons waiting to enter the building and also how many people at any one time are actually in the building (after being processed through security).
I also need to ascertain if particular issues arise at certain days of the week (eg Mon morning) and so need to add a further column which shows the day of week using the date/time data.
A sample of the data is as follows;
Staff No:
Arrival time
Processed time
Exit time
1
26/02/2013 07:13
26/02/2013 07:22
26/02/2013 13:52
[Code] ........
I had thought about trying to display the data using a a stacked bar chart but I still need to look at any particular date/time and ascertain how many persons are in the building without manually counting the rows!
View 6 Replies
View Related
Jan 25, 2010
I want that Excel writes in a row the a sequence of musical notes corresponding to a certain tonality;
I have in one cell the musical notes, which are validated data that comes from a list and I could pick one from this list:
C C# D D# E F F# G G# A A# B
chosen one of the notes, excel would write down in a row the corresponding chromatic scale for that note. example - choosen A:
A A# B C D D# E F F# G G#
As you see, the scale begins with A, and then when it gets to the end of the list (tone B) it goes back to the beginning (C). In other words, what I want to implement it´s sort of a circular validated data.
View 46 Replies
View Related
Sep 27, 2006
I've built a spreadsheet that accurately displays my company's price list. However, from time to time, there are increases and/decreases, by percentages. I would like to know how I can build a formula that would allow me to quickly update the pricesheets by the appropriate percentage, without having to manually do so, one cell at a time.
View 2 Replies
View Related
Feb 3, 2014
I am trying to automatically construct a unique consolidated lists from several other lists. I want this to be formulated in such a way that the mother list is uptodate and reflect all changes made to the subsidiary lists. I have attached an example of what I am trying to achieve.
View 8 Replies
View Related
Jan 15, 2013
I'm working on a macro that does the following:
1. Pull data from the internet
2. Perform calculations on the data
3. Graph the calculated values
- As I run the macro, Excel dies in step 3, where I graph the calculated values.
- It works about 5% of the time and gives me the "Excel has stopped working" error 95% of the time - I can't isolate any difference in the attempts
- I've located the area in the macro where it dies, but I can't identify the exact line since it seems to shift around in this area
- It works perfectly fine when I step through using F8
- I've attempted DoEvents and Application.Wait to debug. Application.Wait for 5 seconds allows the macro to work about 50% of the time and increases in efficiency the longer I wait
- Steps 1 and 2 work perfectly fine (I've used that code in many macros), so I'm pretty sure the issue is in the below code:
I'm running Windows 7, Office 2010
Code:
'Chart variables
Sheets("Summary").Select
Dim ChartRange As Range
[Code]....
View 4 Replies
View Related
Mar 6, 2014
Would like to know if it is possible to create a kind of conditional formatting for graphs. I need to work on a graph and when the value is negative the bar changes of color.
Is there any tool or macro code that can do it?
View 1 Replies
View Related
Feb 26, 2014
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
View 1 Replies
View Related
Mar 23, 2013
I am trying to build a sheet to track deliveries into the company I work for.
We book loads to come in at set times for production, I am wanting a sheet which I can enter the due time and the actual time of the the different companies and then produce a report in graph format to see which are the worst at late or early deliveries.
View 2 Replies
View Related
Jul 12, 2013
How do I paste the first chart into the second chart but maintain the format of the second chart?
View 2 Replies
View Related
Feb 21, 2014
I need to set up an automated process to open an existing .xls file, extract the data from specific cells located in that file and insert it into a totally different file that I have preformatted to accept this data.
Example scenario:
If I have "that_file.xls" and it is unopened, but it contains data in cells "A2 thru F2" that I need to extract... then I have "this_file.xls" , which is open, and set up with all my calculation cells and a specific layout (lets say it has cells "D8, E9, F10, G11, H12, and I13"). I want to be able to add a button to "this_file.xls" to run a macro that would open a file search window where I can select "that_file.xls", and then it will automatically extract the data from "that_file.xls" into "this_file.xls" as follows:
A2 to D8
B2 to E9
C2 to F10
D2 to G11
E2 to H12
F2 to I13
View 4 Replies
View Related
Jun 6, 2012
Essentially, I am working with a database for a school and up until this point, each row matches up to a student with a bunch of information on them. One of these pieces of info is their parent's names. This is a much simplified look at things, but the screenshot below is a basic version of what everything looks like:
Now, the school wants me to split up the parents names from the student's and place them in their own row. I have recorded a macro to do this where you make the active cell the "full name" column for the row you want to expand, and then run the macro. Afterwards, up to two more rows are added, with the parent's names and some data copied. Below is a screenshot of what that looks like: (yellow is the new data generated, and blue is the active cell I marked to run the macro properly)
The issue is that there are thousands of these that need to be done, and I figured there is a way to do this with a macro. The issue is that not every row has parents names to expand out.
In layman's terms, I need a macro that will look at the two columns titled "Father's Name" and "Mother's Name" on each row of the spreadsheet and then, if there is any data in both or either of them, it should set the active cell to the "Full Name" column for that row then run the macro accordingly. After that it should repeat this all the way down, skipping any where neither of the columns have text in them
In the actual spreadsheet, the columns that need to be checked are AW and AX, and the column that needs to be set to the active cell before running the macro is B.
View 5 Replies
View Related
Jan 14, 2014
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
View 5 Replies
View Related
Feb 24, 2007
I know this has been discussed a number of times, but here is my problem
I have three charts in my workbook. I want to attach a macro so that when the chart is clicked it returns to Sheet - Home. I have using the following:
worksheets("Home").activate. But after I protect each chart and the workbook, and save and exit. When I reload the Workbook it has forgotten the assigned macros and nothing happens.
View 7 Replies
View Related
Nov 27, 2011
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
View 1 Replies
View Related
Dec 29, 2011
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.
View 1 Replies
View Related
