Increment sheets number between 2 workbooks

[chemhany]

I have 2 workbooks each of 31 worksheets. The 1st one contains data of which I want to calculate the average in the 2nd workbook separately for each worksheet. I used the function
=AVERAGE(INDIRECT("'C:\Users\lab9\Desktop\H\shift\[AN (32).xlsx]"&1&"'!$B$7:$B$18"))
How can I increment the number from 1 to 31 in that formula when copying sheets from 1 to 31 or when copy the formula from one sheet to another????

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Increment sheets number between 2 workbooks
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.