Define dynamic variables in VBA (name manager

I setup a sheet after the last sheet with name 'SheetName'. This new sheet will have name in the form of '2254121152' as string. This code will change depending on various variable selections in the program. I would then like to define a dynamic variable which would then be used to display a dynamic data range in a list box. Code below is how I have done this, however the Ref. in the offset function will not set as I expect it to do so! In the name manager I should see a dynamic variable set up as:


Spec2254121152=OFFSET('2254121152'!$A$6,0,0,COUNTA('2254121152'!$A:$A),1)


But this is what I get;


Spec2254121152=OFFSET(sheetname!$A$6,0,0,COUNTA(sheetname!$A:$A),1)


Not sure why, Can anyone help??


Thank you