changing sheet reference

New Contributor

I have a workbook with one specific sheet (X) that populates a roster from another sheet (Y) that has all employees locations throughout the month. I'm wondering if there is a way to get it to auto recognize the change in month (so new sheet Y) in sheet X without me having to go in and update sheet X with sheet Y's name? Formula below for reference.

 

=INDEX(November!E5:AH5,MATCH(TODAY() +1, November!$E$3:$AH$3,0))

*note any recommendations for improvement of the formula also. It works currently but at the start of a new month i have to go change the name of the month. 

 

1 Reply

@Mitchell_Green 

You could use INDIRECT to point to the correct monthly sheet based on the current month.  A simple example of referring to A1 on the November sheet:

=INDIRECT(TEXT(TODAY(),"mmmm")&"!A1")

 

Depending on the arrangement of the workbook and the version of Excel you're running, it might be wiser to forego INDIRECT to 'stack' all monthly data and then simply pull the month you need.