Sheet reference not change automatically when drag the formulae

Question

I create a formulae (='1'!$F$8) when I drag fill handle down sheet reference not change automatically I want sheet No. 2 then Sheet No. 3 etc.

Answer 1

If your other sheet names are '1', '2', '3', etc. Then this will work for you.  The result of ROW() or of the calculation of ROW()-2 would be used as a sheet name.

If you put the formula =INDIRECT(ROW() & "!$F$8")  into a cell on row 1 in a sheet then it will show the value from sheet '1', cell F8.  It would be the same as writing ='1'!$F$8

Then if you fill the formula down the sheet, because the row numbers increase, it changes what sheet will be referenced by the formula.  When you copy it to a cell in row 2, then it would be the same as ='2'!$F$8

Answer 2

The sheet name will not change.  The $F$8 prevents the cell reference from changing.  If you wish for the row numbers to increase, change the $F$8 to $F8.

If you always want to reference cell $F$8 on the other sheets you could try this:

=INDIRECT(ROW() & "!$F$8")

The ROW() will return the row number of the row you enter the cell into.  So if you entered the formula into a cell on row 1, it would be same as '1'.  If you enter it into another row, you need to subtract a value from it that will give 1;  for example if you entered the formula into a cell on row 3, you would write it as =INDIRECT(ROW()-2 & "!$F$8").

Answer 3

I want sheet name change automatically only not row or column.