Dynamically populate work week number in excel -

i have requirement generate column headers based on file name in excel. eg: if file name abc_ww201702.xlsx - means file contains data 2nd work week of 2017. every week new file created.

in excel, need column headers dynamically populate next 52 work weeks inclusive of current. eg :

ww02 ww03...ww51 ww01 

i tried use following formula extract last few characters of file name :

=(left(right(mid(cell("filename",a1),find("[",cell("filename",a1))+1,find("]",cell("filename",a1))-find("[",cell("filename",a1))-1),11),6)) 

and further split values year , work week using left/right function. im unable dynamically populate next 52 work weeks.

any appreciated. in advance.

you may try this

in a1

="ww"&right(replace(cell("filename",a1),find(".",cell("filename",a1)),255,""),2)+columns($a:a)-1 

and copy across.

edit:

actually need 2 formulas. 1 formula in a1 , in b1 copied across give desired output.

in a1

="ww"&text(right(replace(cell("filename",a1),find(".",cell("filename",a1)),255,""),2)+mod(columns($a1:a1),52)-1,"00") 

in b1

="ww"&text(if(right(a1,2)*1<52,right(a1,2)+1,1),"00") 

and copy across.