Convert a text string to a linked value

[froggygremblin]

[h=1]I have a report that links data from other worksheets. The names of these worksheets change annually, for example "2018 Jobs Planner 2.0.xlsm", will become "2019 Jobs Planner 2.0.xlsm" next year.[/h]

I am trying to calculate the name of the worksheet for the current year so it does not need to be re-linked each year. I tried to concatenate the year using the "&" operator in this formula


="'C:\Documents\7Oaks\["&TEXT(TODAY(),"YYYY")&" Jobs Planner 2.0.xlsm]Work in Progress'!$C$5"

but of course it just produces this text string

'C:\Documents\7Oaks\[2018 Jobs Planner 2.0.xlsm]Work in Progress'!$C$5.

How can I convert this so it's an actual link to the data in this years workbook cell $C$5?

Thank you for your help
​

 

[Joe4]

Welcome to the Board!

You need to use the INDIRECT function to reference files/sheets/ranges that are built dynamically.
See: https://exceljet.net/formula/dynamic-workbook-reference

 

[Eric W]

Welcome to the Board!

Try:

=HYPERLINK("[C:\Documents\7Oaks"&TEXT(TODAY(),"YYYY")&" Jobs Planner 2.0.xlsm]'Work in Progress'!$C$5","This year's total")

 

[froggygremblin]

Welcome to the Board!

You need to use the INDIRECT function to reference files/sheets/ranges that are built dynamically.
See: https://exceljet.net/formula/dynamic-workbook-reference

Thanks this did the trick =INDIRECT("'["&TEXT(TODAY(),"YYYY")&" Jobs Planner 2.0.xlsm]Work in Progress'!$C$5")

 

[Joe4]

You are welcome.

 

[froggygremblin]

So close but what I needed was the actual data linked. Turned out the INDIRECT function was what I needed.