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!