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?