Solved

Excel 2007 - I would like to create a list based on selected dates

Posted on 2011-02-16
9
182 Views
Last Modified: 2012-05-11
I have data to be plotted that looks like this, but for the whole year:
1-Jan	40
8-Jan	41.42117588
15-Jan	42.89284528
22-Jan	44.4168022
29-Jan	45.9949044
5-Feb	47.62907561
12-Feb	49.32130795
19-Feb	51.07366428
26-Feb	52.88828077
5-Mar	54.76736949
12-Mar	56.7132211
19-Mar	58.72820764
26-Mar	60.81478544
2-Apr	62.9754981

Open in new window

I calculate the dates such as 1-Feb-11 and 28-Feb-11 and I would like to plot the four points within that date range.  

Using the MATCH function I can calculate 6 and 9 as the row numbers, and then with the ADDRESS function I can calculate $B$6 and $B$9 as the endpoints of the data.  

How can I specify Sheet1!$B$6:$B$9 as the range of Y-value data to plot?  I can do it manually with Select Data, but I need to do it dynamically, I suppose using VBA.  Of course I would use Sheet!$A$6:$A$9 for the X-values.

Thanks for your help,

John Fistere
0
Comment
Question by:John Fistere
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 6

Expert Comment

by:nsonbaty
ID: 34913837
you can fix the Y axis as you fix the X axis
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 34914580
=ADDRESS(6,1,,,"sheet1")&":"&ADDRESS(9,1)
0
 
LVL 2

Author Comment

by:John Fistere
ID: 34917186
Thanks for your comments.

nsonbaty,
It is not the axes I am asking about.  It is the selection of X and Y data points.

ssagibh,
That's a good way to specify the date and data ranges.  How do I use them? Simply setting the date range and data range to those cells in the Select Data dialog does not work.  Do I need a VBA expression?

John
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 34973247
Sorry John, I missed your response.

You can make use of dynamic ranges to specify the x- and y- ranges as demonstrated in the attached file. the values 6 and 9 in cells F1 and F2 are used to define the named ranges.

Saqib
Dynamic-Graph.xlsx
0
 
LVL 2

Author Comment

by:John Fistere
ID: 35004552
I have realized that because the Excel graph will plot only the points within the date range of the graph, I don't need to select the points to be plotted.  I can call out the entire data set.

I have no idea how to close out this particular question.  I sort of figured out I didn't need to do what I was asking advice to do.  Should I just abandon the question?

John
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35004856
Even if you realise that you do not need help on this issue any more, if any of the responses addresses your question appropriately you can accept it and it will become part of EE's knowledge base.

If the response does not address your question correctly you can give feedback appropriately to get an appropriate response.

If you find a solution to the asked question yourself before anyone else does then you can post it here and accept your own response.

If no solution is provided within a reasonable timeframe then you can delete the question.

Saqib
0
 
LVL 2

Author Comment

by:John Fistere
ID: 35024470
Thank you, Saqib.

I can't identify any response that I would consider to be the answer to my badly formed question, so I will delete the question.

Thanks for your help.

John
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35024567
John, Your question asks

How to plot points within a given date range. My second response does just that.

Your question also asks

How to specify Sheet1!$B$6:$B$9 as the range of Y-value data to plot? My first response does that.

So you should either tell us what is not right with the responses or accept one or both as an answer to the asked question (even if is badly formed).

Saqib
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35056815
Thanks for the points...and the grade...and the special attention.

Saqib
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question