I have a start & end date selection and want to plot a chart based on the date selections - is this possible without VBA? I have attached a test file.
Thanks in advance
I have a start & end date selection and want to plot a chart based on the date selections - is this possible without VBA? I have attached a test file.
Thanks in advance
You'd use dynamic named range.
x_axis:
=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$E$2,Sheet1!$A$1:$A$31,0)-1,,MATCH(Sheet1!$E$3,Sheet1!$A$1:$A$31,0)-MATCH(Sheet1!$E$2,Sheet1!$A$1:$A$31,0)+1)
Series_data:
=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$E$2,Sheet1!$A$1:$A$31,0)-1,1,MATCH(Sheet1!$E$3,Sheet1!$A$1:$A$31,0)-MATCH(Sheet1!$E$2,Sheet1!$A$1:$A$31,0)+1)
Then in Series formula you'd use it like...
=SERIES(,TestFile.xlsx!x_axis,TestFile.xlsx!Series_data,1)
See attached.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Thank you very much
Can I do something similar to this to do a dynamic calculation instead of adding a dynamic chart? I have 5 rows of data, but will be adding rows each month. Is there a way to calculate return using the first and last values of the dynamic range?
@Aland2929
You are welcome. and thanks for the rep.
@asleischow
Yes, it's possible. But I suggest you start a new thread with sample workbook attached. As per forum rule #2.
2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.
I understand the offset & match formulas and follow the logic but am struggling to create the effect with my real data.
1. Created named ranges with correct ranges.
2. Inserted a blank chart.
3. Selected "Chart Data Range" with Series_data which creates the chart.
How do I apply x_axis to my chart?
I usually create the chart using fixed data range. Then modify series formula.
Click on the series on chart and in the formula bar. You should see something like below.
0.JPG
You can then change series formula to...
=SERIES(,'File Name.xlsx'!x_axis,'File Name.xlsx'!Series_data,1)
Thanks for your time & effort CK76 - I have managed to apply to my chart correctly!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks