Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-17-2016, 06:58 PM
GlowingApple GlowingApple is offline Mac OS X Office 2016
Novice
 
Join Date: Feb 2016
Posts: 2
GlowingApple is on a distinguished road
Default 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
I've seen some examples using the offset function, but I'm not sure how those apply to this case (the range is always the same length, just a different sheet and row number), and nothing I've tried seems to be working (just gives me a blank chart)...



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?
Reply With Quote
  #2  
Old 02-17-2016, 07:18 PM
GlowingApple GlowingApple is offline Mac OS X Office 2016
Novice
 
Join Date: Feb 2016
Posts: 2
GlowingApple is on a distinguished road
Default

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)
This uses the INDIRECT function to get the sheet name from $R2, concatenate it with "$C$1". I'm using $C1 as the reference cell since all of my data sets start at column C. Then the OFFSET function creates a range from the cell that's $R$3 rows down (C7=C1+7-1), and 0 rows left/right. The range is 1 rows high, and 30 rows wide (my data sets are all 30 days).

Then in the graph I set the y value ranges to

Code:
=File.xlsx!Y_Values
Seems to be working well!
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 05:05 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft