I've taken a look at your attachment and found the following question:
I have inserted the INDIRECT function and replaced the cell reference J3:J13 with the above formula.
("J" & A4-B4 & ":J" & A4+C4)
You can see that this should produce an average of 3 cells in each one of the WS's as 12-0 is 12 and 12 + 2 is 14 which should capture 3 cells on each WS.
When I apllied this formula to the first team Atlanta, Excel asked if this was not a formula? It left the first "J" highlighted in the cell address.
I elected to copy the data to the left without formulas to avoid the long address prefix.
This formula is addressing WS's in the same WB as it should.
I'm assuming that I have correctly inserted the WS address of 'Atlanta 17-18'!
I would ask you to concur in theory that with column A dynamically changing with the Countif function importing the number of games played that the numbers in column B and C would inturn direct the formulas to act on the number of cells between.
If you could explain why the Indirect function should allow the forumlas work I would appreciate it.
Hopefully You will be able to discern an error in my interpretation and formula construction.
Your example formula reads:
=Average(INDIRECT('Atlanta 17-18'!("J" & A4-B4 & ":J" A4+C4)))
This is not the correct syntax.
It looks like you want the formula to ultimately simplify down to:
It might be better to focus on first producing the text 'Atlanta 17-18'!J12:J14 and then applying the INDIRECT and AVERAGE functions.
To create the text, you can use the following formula:
="'Atlanta 17-18'!J" & A4-B4 & ":J" & A4+C4
You can see more about how this formula works in the video lesson: Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator.
This is a cross-worksheet formula, which you can see more about in: Essential Skills Lesson 6-6 Create cross worksheet formulas.
Now that you have this text, you can use the INDIRECT function to make Excel treat it as a cell reference instead of text:
=INDIRECT("'Atlanta 17-18'!J" & A4-B4 & ":J" & A4+C4)
The use of the INDIRECT function is explained in the video tutorial: Expert Skills Lesson 4-5 Use intersection range names and the INDIRECT function.
...and finally add the AVERAGE function to calculate the average of the values:
=AVERAGE(INDIRECT("'Atlanta 17-18'!J" & A4-B4 & ":J" & A4+C4))
AVERAGE and other similar functions are covered in: Essential Skills Lesson 2-15 Use AutoFill to adjust formulas.
I hope this helps and you're now able to create the formula you need.