#1
|
|||
|
|||
Dynamic chart ranges
I have a workbook with several sheets. I have a graph on one sheet and I'd like to be able to set the y-value ranges based on the contents of a cell.
Basically I want to have a cell, e.g. $R$2, that has the sheet name, and another cell, e.g. $R$3, that has the row number. So if I enter "2015-09-13" in $R$2 and "7" in $R$3, I want the chart's y-value range to be set to Code:
'2015-09-13'!$C$7:$AF$7 Any ideas? Is there a way to just set the y-values to something like CONTENTS_OF_CELL('Graph'!$R$4) and then use CONCATENATE to build the cell range in cell $R$4? |
#2
|
|||
|
|||
Never mind, got it working! I needed to use the INDIRECT function to grab the name.
So basically I put the sheet name in 'Graph'!$R2 and put the row number in 'Graph'$R3. Then I defined a name called "Y_Values" and set it to Code:
=OFFSET(INDIRECT("'"&'Graph'!$R$2&"'!$C$1"),'Graph'!$R$3-1,0,1,30) Then in the graph I set the y value ranges to Code:
=File.xlsx!Y_Values |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Generating a chart with dynamic series | Officer_Bierschnitt | Excel | 3 | 10-15-2015 12:30 AM |
Dynamic Chart with Non Continuous data | swati_04 | Excel | 0 | 03-02-2015 12:06 AM |
Problem with have a chart with different data ranges | expert4knowledge | Excel | 2 | 09-09-2014 06:27 AM |
How to use named ranges in excel vba? | bosve73 | Excel Programming | 4 | 01-25-2012 09:26 AM |
Dynamic Named Ranges using text | hannu | Excel | 0 | 06-22-2010 04:42 PM |