[Northendzone]

Here is an example of an indirect I set up for something I was working on:

SUM(INDIRECT("'"&$AM$3&"'!Y"&AL6&":Y"&AM6))

AM3 is the filetab name in my workbook
AL6 = 3
AM6 = 6

So this formula adds up the values in Y3:Y6 on the file tab entitled "Whatever"

In my case the file tab Whatever was in the same workbook - you will have to do a little extra work because your are referencing other workbooks.

it is hard to wrap your head around - but once you get it it could change your life