Hi,

define a range name, for example JohnDoe with the following formula

=OFFSET(Sheet1!$B$22,0,0,1,COUNTA(Sheet1!$A$22:$S$22)-1)

Then edit your series formula to be

=SERIES(Sheet1!$A$22,,'test data.xls'!JohnDoe,1)

cheers