I have multiple sheets from 'START' to 'END' as you can see below, the name of the sheets are dates.
I want all sheets to be listed in the 'INDEX' sheet. Every time I add a new sheet between 'START' and 'END' i want the new sheet to be listed.
I want to use the value from cell C5 in each sheet to my Line Chart on the 'INDEX' sheet.
In the X-axis in the Line Chart i want to use the dates.
How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
View 1 Replies View RelatedI have two cells whose values gets updated every minute using a Web Query.
1. Stock Price
2. Time
I would like to create a Line Chart with these two cells. The chart needs to plot and update automatically for every updated value received.
Since i dont have a range of data i have difficulties in setting up the chart.
I have a column of data that I'd like to visualize in a line chart. The cells in the column contain formulas that calculate numbers added to other sheets every month. The adjacent column has the names of each month. New data appears when the data for the month is added.
I'd like to ask the chart to not display the values that are currently zero, because we haven't arrived at that month yet. So, for instance, the cell in the column for November 2011 has a zero, because the range it sums has no data in it yet.
Is there a way to do this without having to go back to each chart every month and change the range to add-in the new data?
I am charting the following data (for a lot of countries, and the data changes based on a pull down menu which selects the country).
Yr1Yr2Yr3Yr4Yr5
Country1167155175188155
Country2145n.a.150140166
Country3142154n.a.170160
What I don't want is the line on the chart plummeting to zero when there is a "-", i.e. no value.
If I chart this instead:
Yr1Yr2Yr3Yr4Yr5
Country1167155175188155
Country2145[.....]150140166
Country3142154[.....]170160
..., where [.....] means a blank cell, then the 145 does now show up at all.
I want to make a line chart that plots a variable over time (say, monthly) and that shows upper and lower limits as the 2nd and 3rd plot. My table layout, boiled down to the bare essentials:
Col 1 = Measurement date
Col 2 = Measurement (pressure, for example)
Col 3 = High limit (never varies)
Col 4 = Low limit (never varies)
The chart/graph in this case would show at a glance how water pressure varies over time and when it goes over (under) a limit. The table has about 50 variables.
I have a spreadsheet where I track income (positive) and expense (negative) items and compare budget to actual amounts. I want to create a simple bar graph comparing budgeted amounts to actual amounts for each item.
However, when I do this, I see all of my income items above the line (pointing up) and all of the expense items below the line (pointing downward).
Is there an option I can select to just use absolute values for the items so they all appear above the line?
I'm trying to do a line chart showing two teams and their cumulative scores over the period of a game. I've attached a sample with mock data. Is it possible to change the x-series of the charts to display custom time intervals like 0 min 1 min, 2 min, 3 min, 4 min, etc? Currently the x-series only has labels for each specific time there was a change in value. I've tried to change the scale but the options that I want aren't there.
View 2 Replies View RelatedI have created three lines between two point.
But if the two point is located at the same horizontal line.
The line cant be produced.
book1.xlsbbook1.xlsb
I have a sheet open that I would like to have a chart like...umm...floating in front of?
First off I want the same "chart" (or block of cells) to be on all sheets of my file so if I enter information into it on one of the sheets it'll be the same on all of them. That's the most important part.
The other thing I'm looking to do with it is I would love for it to remain stationary when I scroll up and down on the sheets. So basically the left half of my screen would scroll up and down but the chart on the right would stay put in my line of vision no matter how far down I scroll.
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
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 created a population pyramid and want to add another chart on the secondary axis which I can do. The part which I seem not to be able to complete is getting the line chart secondary axis to read from the primary y axis. It seems to add a new axis, even when I delete this the line chart does not read correctly.
View 1 Replies View RelatedI am trying to overlay a line chart on top of a stacked column chart. The stacked column chart is a chart where the x axis is dates and the y axis is amount. The line chart is a projected amount that I will reach in few years; the x axis is the date while the y axis is an amount. I can get a chart that has a line and stacked columns, but ever time, the dates get messed up. The line has an extremely wide date range while the columns only have about a month of data.
View 9 Replies View RelatedIs 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.
In the attached file I have the data which shows me the market size in value, my market share in value & I did a calculation to get the percentage. What I need is a chart that shows me the market size in column graph, my market share in line graph and I want the percentages to be showing also.
View 3 Replies View RelatedI have a pareto chart(Bar & Cumulative Line Chart) that I would like to have the line orginate at x = 0 & y = 0 without changing the position of the other points on the line chart.
Basically this is for better visual appearance. When I add a 0 to the table, it skews the alignment of the line chart with the corresponding bar chart.
Attached is the sample data worksheet. Chart 1 is XY type chart using Seconds (2nd column of sample sheet as x-axis from 42510 to 42530). How do I change it to Line chart using Time (1st column of sample sheet as the X-axis) retaining same data from 42510 to 42530 on both primary and secondary axis?. And how do I again change it back to XY chart?
View 3 Replies View RelatedI have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I am working with a nonprofit to set up their financial ledgers. There is one workbook with 12 sheets, one for each month. The goal is to be able to set up a formula that searches through all 12 sheets for every donation that a specific individual has made. For instance, let's say that John Smith gave x amount of money on 1/1/2000, y amount of money on 1/10/2000, and z amount of money on 2/2/2000. This data will appear on 2 different worksheets. I have the following formula, which allows me to look through one sheet at a time:
{=INDEX(Jan!$B:$B,SMALL(IF(Jan!$A:$A=Smith!$A$2,ROW(Jan!$A:$A)),ROW(1:1)))}
Where Column A in each sheet is individual name, and column B is amount donated. Sheet "Jan" is the data for the month of January, and sheet "Smith" is the culmination of John Smith's donations for the full year. Using this array function, I am able to retrieve all data for John Smith in the month of January, but I can't find a way to make one function that searches for all of John Smith's donations in each month. Is there a way to build an array function in VBA that would accomplish this?
Index across multiple sheets returning multiple values
I am building a payroll workbook and I need to build a sheet that will allow me to choose an employee name and the formula will return all the data related to this employee. I found this forumla to do this: =IF(ROWS(...)<=...;INDEX(INDIRECT(...);SMALL(IF(...=...;ROW(...)-ROW(...)+1);ROWS(...)));"")
But in my workbook, the data is on multiple sheets . How do I adapt this formula to look into 12 sheets?
that data would be returned in order of the date of the pay.
file: Test formula on payroll_v3.xlsx
I can create a column chart in excel comparing sales in 06 v's 07 for each month in 07, what I'm aiming to show on the chart is how sales have grown or not over a comparative period. A simple enough graph, I end up with about 24 columns (2 for each month and then a gap between each month).
I now want a line chart to show the performance of an index over the same time period, lets say the FTSE100.
I don't know how to get the line chart under the columns on the same graph, using the same x-axis with a small y-axis to the left. I could propably figure out the y-axis bit if I could only get the line chart below the columns.
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
I have a workbook with multiple sheets in it. One sheet is an Overview sheet, and the others represent the months (JAN, FEB, MAR, etc). On the Overview sheet, I have a few columns of data set up that I need to retrieve from the various sheets within the workbook. The thing is, this data can occur more than once on any particular sheet and it's very likely to occur multiple times throughout the workbook. For example, compressor #1 was blown down on January 1, January 15, February 5, February 9, February 12, April 22, and so on. Compressors 2-5 were also blown down multiple times throughout the year, as well as various other equipment that I have listed out throughout the numerous monthly tabs. On the Overview sheet, I have the columns Equipment, Date, Temp, Begin PSIG and MCF Loss. I'm needing to get the information from those tabs into these columns.
I first tried a simple VLOOKUP. After all, it's worked for so many situations similar to this, why wouldn't it work here? I quickly found that it won't work here because I need to return multiple results, not just the first one it comes across.
When I copy the formula down it seems to return the same results in every cell, not the next result, and the next, and the next, and so on.
I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
i hv following code
(i use generate macro)
my question is how to arrange the code from one line to multiple like :-
following code show in excel macro environment is one striaght line.
' Create new var on yr , and replace 2006 to CY06.
ActiveCell.FormulaR1C1 = _
I'm working with matrix in vba and i'm trying to put all values of a specific line in that matrix in a line in excel. For example:
Matrix =
1 2
3 4
and i want Range("B1:B2") = 3 | 4 (the second line of that matrix).
Is there any way of doing this without perfoming a loop?
[something like Range("B1:B2").value = 'range'(matrix(0,0) , matrix(0,1)) ]
I have a multiple line texts I want to convert it in single line like for Example:
The following contents is to be converted in a single line
contents:
[aaaaaaaa] [bbbbbbb]......so on
[cccccccc] [ddddddd]......so on
[eeeeeeee] [fffffffffff]......so on
Result:
[aaaaaaaa] [bbbbbbb] [cccccccc] [ddddddd] [eeeeeeee] [fffffffffff]....so on
I have multiple sheets and a summary sheet in the beginning. and i need to populate the min value / max value and the avg value for every sheet into the summary sheet.
Example i have a column of numbers in column G and i need the min , max and round(avg) for all sheets in the first summary sheet.