I have data that I am importing regularly and the number of lines of the data is highly variable. I have written code that first inserts a column of data I need for my Y axis, then the second part of my code should select the my X and Y range and change the graph accordingly then thirdly modify the limits of the major axis to the nearest largest multiple of 10 of the data. My sections 2 and three are not working properly.
Here is my code:
Sub Tip_Elevation()
'
' Tip_Elevation Macro
' Insert Tip Elevation Depth (Ft)
'
' Keyboard Shortcut: Ctrl+Shift+I
'Insert column needed for Y Axis
Cells.find(What:="Test").Activate
Is there a way to display only the current month and the past 11 months in a Line Chart in Excel? So if I was to print Jan 2008 Excel graph, it would only display Feb 2007 - Jan 2008 data charting.
The spreadsheet of the data contains data for Jan 2005 - Jan 2008 so far, but only the current month with the past 11 months should display in the line chart depending on what is the current month.
At the moment I've got a chart with 3 series lines on it and each line has 12 points. I need to select the 12th point on each series in the chart and create a label for it. The 3 lines are Budget, Forecast and Actual and often the Forecast line hits the same points as the Budget line in the chart and every time I try to find a way to select the last point on the Forecast line, I end up selecting the budget one because it's the one in the foreground.
I can select the forecast line with the keyboard arrows, but not the last point.
I also try to zoom in to get it but one of my charts has exactly the same forecast line as budget line and I still need the forecast line because the forecast will change in future.
I can delete the budget line then add it back, but this is difficult and one I'd have to repeat many times because I'm always making graphs and labelling the final point in a series is something I'm going to have to do more often.
How do I select the last point on the forecast line if it's directly behind the budget line?
I'm trying to write some code to move the position of a chart in a worksheet. The problem is that the chart has a strange nubmer assigned to it '24' and this increments everytime I delete it and run a macro to redraw it. Is there anyway to reference the chart generically so that I can modify its location automatically in the same macro, using the incrementleft statement?
I have tried 'activesheet.shapes.select' (or something similar - cannot remember the exact syntax I used) etc. but it didn't work.
I've been inserting some rows in some sheets and everything is sound... Until I meet a chart! Charts in general are terrain I yet have to explore (and will be coming Monday)...
I have an embedded chart with a data series and a horizontal line that changes position (y-intercept) by values entered into a textbox. To make the process more user friendly, I would like to allow the user to select this horizontal line and reposition it inside the chart with the mouse. The new y-intercept of the line should then be displayable (textbox, cell, whatever). From what I have read on Peltier's site, I will definitely need to write a new class. Classes in VBA is not something I am particularly familiar with..
I have a large chart made up with hundreds of rows and around 10 columns. Normally I would use certain pieces of data to find the correct row that would provide my answers. I have set up an extra column beside the chart that will put an X next to the row that will provide my answer. What formula could I use to have excel copy the row beside the X, and put it on another sheet in the workbook?
Upon opening the worksheet, I'd like to have a startup macro reassign a macro to a button that is already created. We have an issue with two buttons in a custom toolbar that for some reason default to an old (nonexistent) version of the file when looking for the assigned macro. If I could get an opening macro to automatically reassign those buttons to the proper macros inside the current file, then we wouldn't have to have all possible users login and reassign and resave, which seems to be the only solution at the moment.
Reassigning a macro to a button doesn't show up when doing "Record Macro", and if creating a new button and assigning correct macro while "Record Macro" is occurring, VisualBasic only shows the creation of the button and not the assignment of the macro.
I am trying to make a dynamic chart with a drop down/cell selection. I have a table with names vs. months. Each name/month has an associated value. I want to display a bar chart that I select with a drop down. i.e. If I select January, the chart will show january's data. If I select February, february's. Etc.
I tried to set my series values as: =indirect($C$1) where C1 is the entry cell for the month and the value corresponds to a named range. I get an error that says "That function is not valid."
When I try to put just the named range, excel highlights the correct range but then tells me that "the formula you typed contains an error. Try one of the following..."
I have a bar chart with a few text boxes on it. When I select the chart to print it, the text boxes don't print (or show in print preview). I have to select the cells that encompass the chart to make them print. I have even grouped the boxes with the chart and it still doesn't print them.
How I can make the text boxes print when I select the chart only?
I have a chart, I need to select a cell beneath that chart. It can be the top cell or top left cell. So is there a way, by using vba, to know which cell is the top or left cell being covered by a chart
Seeking a way to automatically select just the populated range of cells in Cols A, B and C starting with row 2. In this sample there are 48 however this changes.
I have a line chart that tracks daily inventory and daily distribution. The chart also has a static value of Max inventory and Min inventory levels. Is it possible to shade the area between the Min and Max levels on the chart a different color from the rest of the chart. For example the chart has a white background and I would like to shade the area betwen the min a max a light blue across the width of the chart.
I have made a simple procedure (which will extend to make something that I have to make but for time being) it asks user to select a chart type in a userform using option buttons and then the chosen chart type is taken as the chart type and makes the chart.
problem is that the typechart - variable to identify the chosen chart type - is not being recognised. Here's the coding, the variable is not passed (in yellow bolded) from one procedure to another?
Private Sub NextButton_Click() Call ShowType If bFlag = False Then MsgBox "Not Selected" Call ShowType Else Call MakeChart End If End Sub
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
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.
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.
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.
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.
I want to add a user-form on a chart, which will have check-boxes that will allow me to select series(lines) that I want see and compare in a chart. Currently my line chart has 24 series (Lines) which makes the chart very difficult to view and looks very busy. How do I add list of check-boxes that will allow me to select one or multiple lines that I want to see at a time?
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
creating a spreadsheet for work which is almost working a treat Unfortunately, when a row of data moves from one sheet (Queries) to another (Archive), data validation is lost. Initially I thought I daidn't have it set up on the Archive sheet, but on moving it back to queries (by use of a macro) the validation is still not working. how to keep validation rules WITH data when it is moved please? I will upload my file when I get home from work....network restrictions prevent me doing it here!
I'm essentially getting a lot of data at the moment, which has a few orders people have made on my website.
Essentially, think order id, address etc and then all the products the customer has ordered.
However, the part which includes what the customer has ordered creates multiple rows of data, with the order ids etc duplicated. What I need to do is consolidate this into 1 row. So to add additional columns instead of rows.
The reason fro this is I want to mail merge the data into an invoice and mail merges work of 1 line of data at a time. I've attached an example, any way to do this?
(Attached to this post / or linked here: [URL] ....)
I have 3 columns Z, AA, AB. The heading for Z1 is “A”, AA1 is “B” and AB1 is “C” In column Z2:Z2000, there is a mix of A, B’s and C’s. I want A to stay in Z1 column, B’s to goto AA1 and C’s to goto AB1, also I want this added to a macro that I previously created, so everything happens with one push of a button
Now for save, not sure if this is possible or not, if I can have this added to the macro as well that would be great. When I push my macro button, the file saves to “Dec (today’s date) DB (81).xls” The number 81 is the total count of A, B’s C’s, this # will change depending on how may A, B’s and C’s there are. I really hope there is a way of doing all this
Essentially, there are two columns I am dealing with. One is "Sales Rep" and it lists all of the sales reps employed by the company. The other is "Zip Code" and that will list all of the zip codes that sales rep is responsible for.
Now, I have a row of data, all of those zip codes listed out, that each rep is responsible for, but my supervisor wants all of the zip codes listed in one cell, in that second column. Example: (02018, 34098, 16711).
The commas are not necessary, but is there any way to get this done other than manually entering them?
I have a workbook with 5 Tabs. One of these tabs is "Completed" (for completed work) The other tabs are names of Managers and the tabs contain information about who is doing what work for the Manager and information about it.
What i would like to do is in column F on every sheet is the "status". I would like when the "status" is changed to completed, to have VB code move that entire record to the Completed tab.
I think its possible i just don't know how to do it.
I am trying to do what I have quoted below. In particular,I have the two userforms set up so that I open one and then click a checkbox that opens the second userform. I would like the data I enter into a texbox in the second userform to populate a text box in the first.
Originally Posted by dominicb Good evening scott92
Sounds like you want to dump the contents of textbox1 into a public variable and force textbox 2 to pick them up from there. You might have a problem deciding exactly when textbox2 is to update - ie what event you're going to hang it from. Are both userforms visible on the screen at the same time? is the updating to take place in real time?
