Matching a sheet to reference depending on current date

[MrBurn5]

If I have a workbook that has 365 sheets that each reference a date and are labeled in a MM/DD format; is it possible to reference those sheets depending on the current date which is subsequently their name?

For example, this formula takes a running total from a previous sheet titled "Sept 16" and adds today's totals in cells Q22:Q23.

	Q

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]60[/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]27[/TD]

</tbody>

Sept 17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]Q22[/TH]
[TD="align: left"]='Sept 16'!Q22+Q20[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]Q23[/TH]
[TD="align: left"]='Sept 16'!Q23+Q21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


But... I'd like to have the running total in a separate sheet that contains all the performance data. Let's say that sheet is called "Master".

Is there some sort of formula I could use in the 'Master' sheet, that would work like this:

='yesterday's date'!Q23+'today's date'!Q21
(ie, ='Sept 17'!Q23+'Sept 18'!Q21)

where the formula would automatically update at midnight?

 

[James006]

Hello,

Most probably ... the Indirect() function will do this job for you ...:wink:

Should you need to dig into this function ...

https://www.contextures.com/xlFunctions05.html

Hope this will help

 

[MrBurn5]

I'll give it a shot once I wake up and have my coffee. I've never known any other way to reference a sheet besides how I've been doing it so I'm open to any other option that might exist.