Adding Cell Values up to create an indirect reference to another sheet

[arniebun]

Hi was wondering if anyone can help, or if this is possible in excel,

so I have new files every day that will be created daily with this example file name, 20-09-2020-mysheet.csv.

in the sheet i want to reference to I have in A1 the dates formatted 20-09-2020, so what I want to do is create a dynamic reference to all my sheets like this example ='20-09-2020-mysheet.csv'!$B$2
so what i need to do is get 20-09-2020 and add it to -mysheet.csv'!$B$2 to create my reference to my sheets being produced daily. so I can copy this reference to auto reference the next 5 years. I would go in and do it daily, but I have a number of sheets I need to do this with and will defeat the object.

if you could help would be massively appreciated.

 

[jasonb75]

From your example, it sounds like you need

=INDIRECT("'"&TEXT(A1,"dd-mm-yyyy")&"-mysheet.csv'!B2")

 

[arniebun]

thank you so much Jasonb75 that is perfect