Looking to change sheet reference in formula

[Jamriv]

I have a primary sheet which is printed for meetings to show the progress of a particular run at work. I set it up so it would pull data from multiple summary sheets and can all remain in one file for ease of reference. I want to be able to easily change the sheet number referenced as easily as dragging formulas elsewhere.
The number I want to change is in parentheses and each sheet is named "Summary" except for the first sheet which is where the results are kept.

My current formula is ='Summary (1)'!F2 but I have to manually go down the line and change (1) to (2) and then to (3) and so on and so forth. Is it possible to make the reference number change? I don't mind naming the sheets something different, but I would prefer to keep my system of multiple sheets for traceability reasons and be able to easily add similarly named sheets on should the need arise. Thanks for the time and sorry if this subject is trivial, I'm just beginning to really delve into excel at work.

 

[steve the fish]

This will drag down ok:

=INDEX('Summary (1)'!F:F,ROWS($A$1:A2))

 

[steve the fish]

This will drag down ok:

=INDEX('Summary (1)'!F:F,ROWS($A$1:A2))

Oh ive misunderstood havent i?

 

[steve the fish]

You could use an indirect:

=INDIRECT("'Summary ("&ROWS($A$1:A1)&")'!F2")

 

[Jamriv]

Oh awesome, INDIRECT worked as intended. Thank you for your help! I was struggling to get this to work properly without it being a nuisance. Just for my own personal knowledge; the ("&ROWS($A$1:A1)&") could be used in other instances should I have another type of document that aims to do the same thing?

 

[steve the fish]

When you drag =ROWS($A$1:A1) down the sheet you will notice it just produces a sequence 1,2,3,4 etc so in your case Summary (1), Summary (2) etc.

 

[FormulaMensch]

Hi. I'm trying to do the same thing as the poster here, but my sheet names are unfortunately not numbered, they are all letters, and alphabetical: AE, AL, AR etc.
Can this solution be adapted for non numerical logic like this, or do I need an alternative approach?