I have a Google Sheets that gathers values from three other sheets that have strict naming rules: the name of these sheets contains the year in a YY format followed by an underscore, followed by the month in a MM format followed by an underscore, and finally one of the three posts.
So it looks like 17_12_TISZT for the December, 2017 post1 sheet or 18_01_APOLO for the January, 2018 post2 sheet.
Is it possible to create a string that changes dynamically with the ongoing date (year and month) so I won't have to manually find and replace the references?
I have tried constructing the string needed with the following formula:
=RIGHT(LEFT(TODAY();4);2)&"_"&MID(TODAY();6;2)&"_TISZT"
this gave me 17_12_TISZT; exactly the name of one of the three Sheets I need to reference.
My formulas that need to use the reference, however, cannot seem to work this way. Instead of referencing '17_12_TISZT'!RANGE I tried:
'RIGHT(LEFT(TODAY();4);2)&"_"&MID(TODAY();6;2)&"_TISZT'"!
and some other ways like this, but ran into errors all the time.
What am I doing wrong?
Can this type of cross-referencing be accomplished in any way?
