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

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?

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

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

Seems to be working well!