Using a graph with 1 dynamic range

[Corleone]

Evening forum - I have an excel question for you

I have a line graph made up of 2 rows of data-
Row 1 contains the headers
Row 2 contains the £ of the forecast budget per period - running from Cell B2 to AA2
Row 3 is the actual spend per period - Also running from cell C2 to BB12

I would like to make the range dynamic for row 3 only based on the period number that i select on a dropdown in Cell A1.

So for example when i select this period (11) in the drop down it automatically selects the first 11 cells in row 3 (C3:M3) and then next period when i change the dropdown to select "12" the range extends across another cell to cover the first 12 cells (C3:N3)

 

[Corleone]

I nearly have this cracked but im falling at the final hurdle - im testing it out in a separate spreadsheet

I have a named range called graph which refer to the following formula - =OFFSET(Sheet1!$A$3,0,0,1,Sheet1!$A$1)

however, when i try to apply this named range to a series value i get this message

as you can see below - it recognises the formula (see row 3) but the graph is not having it

any help appreciated

 

[Corleone]

Resolved it by adding the filename before the range =test.xlsx!graph