?
Solved

Excel Charts for Dynamic Date Range

Posted on 2016-09-13
7
Medium Priority
?
174 Views
Last Modified: 2016-09-15
Hi Experts

I have an excel sheet in which I have the data for a few stocks and I need to create the Line Charts for them. But the Line Charts needs to be made in such a manner, that the “Number of Days to be plotted” do not need to be fixed beforehand. If I have 5 days data some day, then it should plot that 5 days data and if I have 20 days data on some other day, then it should automatically be able to plot those 20 days data, without any manual modifications from my side.

I have explained the requirement in very clear and detailed manner in the attached sample file.

Please suggest various methods by which this could be done.
If you have any doubts, then please ask them, and I would explain in more details.

I am using the following software versions -
Microsoft SQL Server Management Studio version-  12.0.2000.8,
Microsoft Office 2016 x64
and Windows 7 x64

I have attached the Excel File having the data and the sample charts.
Excel-Chart-for-different-durations.xlsx

Thanks
0
Comment
Question by:happy 1001
[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
  • 3
  • 3
7 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41795534
HI,

pls try with a table

Regards
xcel-Chart-for-different-durationsV.xlsx
1
 

Author Comment

by:happy 1001
ID: 41795585
@Rgonzo1971, thank you so much for your suggestion. But unfortunately I will not be able to use the methods of TABLES in my case, because I have uploaded the most simple sample for illustration purpose. The real data is very big and has data for hundreds of symbols and I need to keep empty columns between every two symbols, because I would be using those columns for some calculation type of work.

So if possible, please suggest some alternative methods in which I do not need to convert the data into table, and yet I will be able to plot all the data on the line chart, on different days, having data for different number of days.

Thanks and regards
0
 
LVL 2

Accepted Solution

by:
psteff earned 2000 total points
ID: 41796038
You can use named ranges.  So, using the names and locations of your data and charts, set up the following named ranges:

ABIRLANUVO would be            =Sheet1!$E$9:OFFSET(Sheet1!$E$9,COUNT(Sheet1!$E$8:$E$5000),0)
DATES (for the dates) would be    =Sheet1!$D$9:OFFSET(Sheet1!$D$9,COUNT(Sheet1!$D$8:$D$5000),0)

Note – you may need to change the $E$8:$E$5000 and the $D8:$D$5000 for the max length (5000 rows) of what you would expect your data to be.

Then in the chart in “Select Data Source”, under Legend Entries (Series), Series values, type in
='Excel-Chart-for-different-durations.xlsx'!Abirlanuvo

And in the Horizontal (Category) Axis Lables, type in
='Excel-Chart-for-different-durations.xlsx'!Dates

Note: the first part of each of these may need to be changed based on what the name of your actual workbook is.

This should give you the dynamic charts that you are looking for.
1
Technology Partners: 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!

 

Author Comment

by:happy 1001
ID: 41797638
@psteff, thanks a lot for interesting suggestion. I tried to implement it. First I created both the named ranges as told by you -
Named-Ranges.png
And then tried to create a chart and tried to add the named ranges into their proper place, as suggested by you. But it keeps on showing this error -
Reference is not valid. Reference must be to an open workbook.
Error.png
I am using the exact file, that I have uploaded in the first post, with exact same file name.
I tried to use different naming methods like -

='Excel-Chart-for-different-durations.xlsx'!Dates

='Excel_Chart_for_different_durations.xlsx'!Dates

='Excel Chart for differentdurations.xlsx'!Dates

But all of them is showing me the same error message.

Can you please suggest, what might be causing the problem here.

Thanks a lot for the help.
0
 
LVL 2

Expert Comment

by:psteff
ID: 41798044
Strange – how Excel seems to be working.

I originally had it working in my copy, but when I went to try it now – you are right, it did not work putting just that information in.  So, I tried it a different way.  I put in the following, respectively:
='SHEET1'! ABIRLANUVO
='SHEET1'!DATES

It then worked – but when I looked back in those areas, it replaced the ‘sheet1’ with the workbook name.  Why Excel is acting this way in this area is a total mystery to me – but it appears that you have to put the sheet reference in when initially entering in the information.

I hope it now works for you.
1
 

Author Comment

by:happy 1001
ID: 41800184
@psteff, thank you for providing the additional information. The new method works fine.

Thanks a lot
0
 
LVL 2

Expert Comment

by:psteff
ID: 41800232
You are welcome.  Glad to be of help!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

764 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