copy formulas with workbook and sheet names as variables

[cnfsdclrk]

I have 138 Excel Workbooks with 12 sheets each. That’s 1656 sheets I need data from plus 4 totals from each, so in total I need 6624 formulas. The Sheets are named 1-12, and the workbook names are "Last name, First name." I need $J$15, $J$16, the sum of $J$15:$J$16, and $K$22 from each sheet.

I have created a separate excel to gather the data, but I’m not sure what formula to use to copy down because I’m not sure how to make the sheet name or the workbook name a variable. I started using the following formula, but I need a more efficient one.

='[Last name, First Name.xls]1'!$K$22

What formula should I use to automatically retrieve the data? Are there steps I can take to make this an easier process in the future?

Thanks for your help!

 

[cnfsdclrk]

*I want to copy the formula, changing the sheet name:

='[Last name, First Name.xls]1'!$K$22
='[Last name, First Name.xls]2'!$K$22
='[Last name, First Name.xls]3'!$K$22

As well as:

='[Last name, First Name.xls]1'!$K$22
='[Last name2, First Name2.xls]1'!$K$22
='[Last name3, First Name3.xls]1'!$K$22