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
Please reply to this thread with any new information or opinions.

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 03:22 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