Change Sheet but not cell reference when dragging down formula

[Skycop]

Hello all, I have tried searching for what I believe to be a simple answer, but I can't seem to find what I am looking for. I have a very simple spreadsheet where I have sheets labeled 1-50. I then have a summary page which references different cells on each sheet. ='1'!$F$2 for example.

What I am trying to do is when I drag down through all 50 cells to get the cell reference to change accordingly where on row 2 it would read: ='2'!$F$2

I have tried numerous times and am at my wits end. Any help would be greatly appreciated. Not only does this need to be repeated for the 50 rows, but also needs to be repeated for different columns, but I think I can figure it out from there if someone points me in the right direction. Thank you very much.

 

[harryzhere]

Hi Domenic,
This might be an old post but I need help with a similar issue here.
My formula is ='[Ferm Master 2017 (007).xlsx]103'!$C$3. Ferm master 2017 (007).xlsx is the name of the document. 103 is the tab. So, I would like everything else to be constant except the tab number (104, 105 etc.,) when I drag it down.
Thanks everyone for your help in advance!

 

[b4d6oy]

Let's say that the formula is going to be entered in B2 and copied down, try...

B2, copied down:

=INDIRECT("'"&ROWS($B$2:B2)&"'!F2")

Change ROWS($B$2:B2) according to your starting cell. Also, if you want to be able to copied the formula across as well, try...

B2, copied down and across:

=INDIRECT("'"&ROWS(B$2:B2)&"'!"&CELL("address",F$2))

@Domenic
Thanks its working...
@Skycop
here i m giving the break down:

The req was like this: '1'!$F$2
In the formula
1st step " ' " = '
2nd step &ROWS($B$2:B2)& = 1 ( if u change B3 instead of B2 it will be 2)
3rd Step "'!F2" = '!F2 ( $ sign is not req)

so after 3 step we found : ' + 1 + '!F2 = '1'!F2 ( the formula u req )

 

[Mishaelka]

I have used it for a similar situation. How do I change the formula in a way that the cell is blank if there is nothing entered in the cell that is referenced?
Could any of you help me with this? You can use the same situation to explain. Ill make the changes accordingly.