Create Dynamic Line Graph Using Week And Year Numbers Stated In Another Sheet?

Apr 7, 2014

I want to create a dynamic line graph using week and year numbers stated in another sheet.

e.g.

Start Year - 2012
End Year - 2014

Start Week - 3
End Week - 12

The top 2 rows above my graph data are as below:

Year - 2012 2012 2012
Week - 5 6 7 etc.

This works fine if the start and end year are the same but if it's greater than one year, it doesn't recognise that.

View 7 Replies


ADVERTISEMENT

Macro Code To Create Line Graph

May 2, 2014

I have a macro code that will create line graph referring the data given in defined column A1-C4,

Code with Static column range:-

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet2'!$A$1:$C$4")
ActiveChart.ChartType = xlLineStacked

I tried to modify the above code, so it will refer undefined/dynamic data column, but getting an error during execution "Run Time Error - 424:" "Object required"

Code with Dynamic column range:-

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=ActiveSheet.Range("a1", _
ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select
ActiveChart.ChartType = xlLineStacked

View 1 Replies View Related

How To Create Mixed Line / Bar Graph With One Data Point

Jan 6, 2012

Ok, so here's the trick:

I know how to create combo or mixed graphs with line/bars on single axis, and secondary axis. I even know how to do mixed stacked and unstacked columnar charts...

But how do you depict in single view graph

a single bar (1 datapoint) with a single line (upper control limit e.g. target).?

And I don't want to to use shapes to draw the target line.

I want the target line to be automatically plotted by Excel...

View 9 Replies View Related

How To Create A Graph Where Date Starts On July 1st And Runs Through A Full Year To June 30

Jan 14, 2014

I am trying to create a graph where the date starts on July 1st and runs through a full year to June 30. No matter how I sort the dates in the cells, the graph still wants to start in January. You will see from the attached picture the very right lines up with the very left, where these should actually be meeting in the middle. as to what I need to do? Please see photo and dataset.

Chart.jpg
DataSet.xlsx

View 3 Replies View Related

Convert Date To Year/week Of Year/day Of Week

Apr 6, 2007

Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)

View 9 Replies View Related

Line Graph: (line Graph To Display An Amount Over Time)

Dec 30, 2008

I want to use a line graph to display an amount over time - that's the easy part. On the other hand, I would like to have to group the lines based on a value.

A short example:
Imagine you own 3 different stores and you're selling oranges. So your table looks like this:
http://img179.imageshack.us/my.php?image=orangeshm4.jpg

Now I'd like to have one graph (3 different graphs won't work as the rows increase -I need to select the whole column as data source):

Date on the x axis,
Oranges sold on the y axis,
and one line per store (e.g. a green one for store A, a red one for B and a blue one of C, doesn't matter).

View 2 Replies View Related

Create Line Graph That Will Incorporate Multiple Columns Of Data In One Series Of Data

Sep 27, 2011

I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.

I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.

Example Below:

Column AColumn B Column C159261037114812

Now in the example all of the values are x values.

View 2 Replies View Related

Create Time Sheet - How Many Hours Worked In A Week

Sep 25, 2013

I'm trying to create a time sheet to calculate how many hours worked in a week, Once it reaches 40 hours, The excess over 40 hours goes into a "overtime" cell. The "40" hours remain in the regular hours cell.

Attachment 267704

View 14 Replies View Related

Line Chart With Year On Year Comparison

Sep 29, 2006

I know that in order to draw a chart where a data line for a certain period is compared with the same period the previous year, one should have the 2 sets of data of different year side by side columnwise. However, is there a way where I could still churn out the same line chart when the data is all on a single column?

View 9 Replies View Related

Line Graph Not To Continue The Line If There Is Nothing In Row 10 Of The Table

Nov 6, 2009

With the attached spreadsheet i cannot workout how with the line graph not to continue the line if there is nothing in row 10 of the table.

for eaxmple if there is no value in cell M10, i would like the actual addtion line to stop at the last value on the graph that was in L10.

View 4 Replies View Related

How To Change Line Thickness In Line Graph

May 21, 2010

I have a chart sheet with a line graph that contains two series: I want to change their thickness, but I am not able to format it with VBA.

View 6 Replies View Related

Create A Dynamic Array So That When A User Enters The First Sheet Name Via An Input Box

Nov 20, 2008

I'm trying to create a dynamic array so that when a user enters the first sheet name via an input box, excel will know to select the other sheets with ending (2), (3), (4) etc.

Currently this code works only if there are 4 sheets present (as I set this way), anything lower/higher is out of range.

How do I make it so that it will select sheets if there are less than 4 sheets present?

I tried nesting, but I pretty sure this array needs redimming or something. Is ubound a better option?

View 5 Replies View Related

UDF To Output Tax Year Week Number

May 4, 2006

I can retrieve the weeknum of any particular date using =WEEKNUM(x) or =TRUNC(((x-DATE(YEAR(x),1,0))+6)/7)

But I'm looking for a UDF to output the tax year week number where the tax year always starts on 6th April. The std weeknum for 6th of april pretty much varies around week 13 but as it varies I don't ever get a strictly true result by deducting 13 from WEEKNUM().

View 4 Replies View Related

List All Days For The Week For The Year

Oct 11, 2009

i have the year in cell A1 (Just The Year)

what i was looking for is a formula that will list all the days of the week..

IE if i pick Monday form a drop down list than it would like all the mondays for all twelve (12) Months

so Jan Column would say

5
12
19
26
31

and so on for each month than if i picked a different day it would do the same

Using Microsoft Excel 2003 On Windows Xp Home Edition

View 9 Replies View Related

Date To Week Number & Year

Oct 9, 2007

I've done a search on here to find out how to convert a date to a week number & found this: - =WEEKNUM(A1) which works fine, But I also want the result to display the year.

So 08/10/07 becomes WK40-07

I can't see how to do it!

View 5 Replies View Related

Recognize A Series Of Dates As A Particular Week In The Year

Nov 12, 2008

My intentions are for Excel to recognize a series of dates as a particular week in the year. For example: 12/28/2008 thru 1/3/2009 equals Week 1, 1/4/2009 thru 1/10/09 equals Week 2.

I cannot for the life of me calculate a working formula.

View 7 Replies View Related

Week Number Based On Year Start 01/10

Jan 9, 2008

My finacial year starts on the 1st of October of each year and ends 30th of September each year.

My week is from Monday to Saturday - though for calculation ease: lets say that my week is from Monday to Sunday.

I need to calculate the week numbers of a date based on the above two criteria.

I understand that I need to ascertain the day of the start date first i.e: did 01/10/07 fall on Monday, Tuesday etc. - In fact it was Monday!

so for my year 07/08:Week 1 was 01/10/07 to 07/10/07Week 2 was 08/10/07 to 14/10/07ETCSo in the attached worksheet - what formula can I use to populate column D - by using the data in column E?

I.e. All I want to do, is input date into E and B, C, D will be calculated automatically. It would be great of excel could populate A - aswell ;-)

View 11 Replies View Related

Calendar From Formula :: Nth Day Of Week For A Month And Year

Oct 12, 2008

I'm using this formula from

[url]

Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

I would like to be able to change the year and month in A2 and B2 and have the calendar change.

I will be inserting rows between the weeks to return appts, if I can get this part working.

I could make a new tab for each month, but I thought I would give this a try....

View 9 Replies View Related

Year,month,week,days Calculation

Aug 3, 2006

What I am trying to to is calculate the number of Years, Months, Weeks, and Days from one date to another. So far I can calculate years and months accuretly but I'm having trouble with the days and can't seem to figure out how to do the weeks.

I'm using the formula: =DATEDIF(B1,B2,"y")&" Year(s), "&MOD(DATEDIF(B1,B2,"m"),12)&" Month(s), and "&(MOD(DATEDIF(B1,B2,"d"),365))&" day(s)"

B1 is the current days date
B2 is the entered date

What I am getting when I enter the date 9/14/09 with the current date (8/3/06) is 3 years, 1 month and 43 days. when it shoud only be 3 years 1 month and 11 days. Any date I enter the days are not right. I can't seem to figure out what I am doing wrong. Also I'd like to get the weeks to come up also. Such as Start date 8/3/06 Entered date 10/19/09. What I want to see: 3 year(s), 2 month(s), 2 week(s), 2 day(s).

View 3 Replies View Related

Find Last 12 Values Based On Week Number / Year

Jul 9, 2014

I am trying to fill a table of the last 12 values for the purposes of creating dynamic charts. I remember last time i used named ranges, offsets etc etc but been too long to remember how.

Ive attached a worksheet to explain it better.

I should probably mention, I want to be able to change cells C1 and C2 to update the values. Everything else wil be rather static.

Attached File : Test.xlsx‎

View 5 Replies View Related

Get Date From Week Number And Year ( Fiscal Calendar)

Jan 22, 2013

I'm having a data only pull week number and year. We are using Fiscal calendar starting in July. For example, A1 = Week number and A2= Year. How to set up a formula to retrieve a date for this? If A1 = 2 , A2 = 2013, the date will be 07/14/2012. I want the date pull of on Saturday every week.

View 6 Replies View Related

Macro To Link Cell To Sheet & Cell Stated By User

Jun 3, 2008

I have a macro that inputs a sheet and cell from the user. when the macro tries to make a formula it outputs =data!'AE3'. I need it to output =data!AE3

Dim Sheet As String
Sheet = InputBox("Take Data from where?")
Dim startcell As String
startcell = InputBox("Start Cell?")

ActiveCell.FormulaR1C1 = "=" & Sheet & "!" & startcell & ""

View 3 Replies View Related

How To Add Series Without The Graph Line

Apr 14, 2014

Is there any possible to add a series to a graph table without having the line in the Graph. i just want to show the difference for information and not in the graph?

i have tried some different things (like having the difference in white so it is not visible, formatting the Axis etc)

See attached picture. Excel 2.JPG

View 4 Replies View Related

Adding Line To Bar Graph?

Jan 20, 2014

I am trying to recreate the screen grab that I have copied and pasted in (purple bar chart). I am to create the bar chart, but I am unsure how to create the dotted line which is the national average.

View 3 Replies View Related

How Do I Not Graph A 0 Value On A Line Chart

Oct 26, 2009

I have created some charts for my work and one of them is a line chart that spreads across one year with values for each month. Well they want the graph to always show one month ahead of where we are out. So there is actuals in for Sep. but then the line graph drops all the way to 0 for Oct. I don't want to have the graph bottom out when there is a zero. Does anyone know how to fix this?

View 9 Replies View Related

Stacked Bar & Line Graph

Sep 20, 2006

how to create a stacked bar & line graph together.

I know by default, I can choose to have a stacked bar, OR a line graph, OR a basic bar & line graph.

But I need to take a step further and have a stacked bar WITH a line graph.

options in which I could combine the above graphs?

View 4 Replies View Related

Formatting Line Graph

Feb 15, 2007

how to have different format on a line grapgh. I have a series of data points for sales figures. The first 10 are actual data and the next 5 are projections. I want to have a line graph with solid color format for the actuals and a dashed format for the projections.

View 3 Replies View Related

Line Graph With 2 Lines Per Person?

Sep 15, 2013

I need to create a line graph that has 2 lines for each person, one with the amount, one with the points. The X-Axis is by Weeks going up. How can I do this without making a separate series for each? I've attached an example.

example.xlsx

View 1 Replies View Related

Stacked Bar Graph With Average Line

Mar 24, 2009

We are trying to create a stacked bar graph with an average line. Each time we try to add in the average data to the graph it adds it to the stacked bar. The stacked bar should represent the inpatient & outpatient cases at a hospital for the current year (months showing across the as columns). The line should represent the previous 2-yr average total. Anyone have an idea how to add a line to the stacked bar graph? It has us stumped.

View 3 Replies View Related

Line Graph In Descending Order

Dec 8, 2009

How do i achieve the graph that is on the pdf with the data in the workbook i've attached. i cant work it out?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved