I would like to use vlookup to get data that is on several different pages.

I realize I could do this by =SUM('ATStemplate1:PH TEMPLATE 2'!F31), where I
change the F31 to be the cell I want for each different column, however I am
already out to column BA. When columns are added or deleted (by date) than I
run into problems.

What I would LIKE to do is something similar to this
=sum(VLOOKUP(b$4,'3005:3007'!$1:$65536,6,FALSE))
Where "6" will change depending on the date (which I accomplish with a
Hlookup)

I thought about
=sum(VLOOKUP(b$4,'3005:3007'!$1:$65536,6,FALSE)),V LOOKUP(b$4,'3006'!$1:$65536,6,FALSE)),VLOOKUP(b$4, '3007'!$1:$65536,6,FALSE)))
However I have like 25 tabs, which would make this formula very large, not
to mention the addition and deletion of tabs.

Is this even possible other than the long hand written way