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.