indirect function looking for worksheet named after current year

Consolidating some sheets in my workbook and trying to edit this working formula by using the INDIRECT function. How can i replace SHEET1 with the sheet name = to the current year in this VLOOKUP formula?

=VLOOKUP($B7,'SHEET1'!$N$6:$AD$17,1+MATCH(MONTH($E7),COLUMN('SHEET1'!$N$6:$AD$6),0),0)

I've got a number of other formula's that work using that INDIRECT function, but I can't seem to make it work with the VLOOKUP function above.

Example: =SUM(INDIRECT(YEAR(TODAY())&"!TableName"))

The VLOOKUP function above (It is in cell D7) searches Column N on SHEET1 for value = to B7, then looks for the column header = to the month in E7. The value in D7 does not change until the specific mmm/dd I have in E7.

Thanks for the help. So far so good, but needing to test out all the conditions....

If the due date is today it works
If the due date is next month it works
If the due date is tomorrow (same month as today), still testing

From another post I made a while back on here, it needs to follow at least these 2 rules...

Basically, my first spreadsheet would display the same amount in column2 for the entire month and would not change until the first of the month. I then made adjustments so that column3 (due dates) would automatically adjust to the next due date when the date passed and then column 2 cells would adjust accordingly.

If $E6 > today and $E6 =month(today), then display the cell value found via lookup on Personal Budget sheet month(today)
If $E6 > today and $E6 is next month, then display the cell value found via lookup on Personal Budget sheet next month

Doesn't really matter if $E6 < today. Past dates will never be displayed. That's why I left it out.

This formula passed all the tests mentioned above when pointing to the old sheet1

VLOOKUP($B6,sheet1!$A$4:$M$6,1+MATCH(MONTH($E6),COLUMN(sheet1!$A$4:$M$4),0),0)

So I'm wondering if your suggestion will do the same. We will see when a due date of 6/29 hits tonight.

Testing it out. Will let you know. Thanks.

I think it is working. Thanks!