Copying worksheet cells into another worksheet using autofill

[SunnySD]

I have 31 worksheets to copy into another worksheet and the cell # is the
same. What's the best formula to use to autofill?

Currently, I am getting this:
='1st'!$F$21
='1st'!$F$21
='1st'!$F$21

I need it to show this:
='1st'!$F$21
='2nd'!$F$21
='3rd!$F$21
and so on...

Any help is appreciated!

 

[Sheeloo]

Put the values 1st, 2nd, 3rd,... in Col G

Put the following in A1 in the sheet you want the values to be;
=INDIRECT("'"&G1&"'!$F$21")
and copy down...

 

[Gary''s Student]

Assuming Sheet1 thru Sheet31, use something like:

=INDIRECT("Sheet" & ROW() & "!$F$21") somewhere in the first row and copy down

 

[Gord Dibben]

If the sheets are truly named 1st, 2nd, 3rd etc................

Add this UDF to a workbook module.

Function OrdinalNumber(ByVal Num As Long) As String
Dim N As Long
Const cSfx = "stndrdthththththth" ' 2 char suffixes

N = Num Mod 100
If ((Abs(N) >= 10) And (Abs(N) <= 19)) _
Or ((Abs(N) Mod 10) = 0) Then
OrdinalNumber = Format(Num) & "th"
Else
OrdinalNumber = Format(Num) & Mid(cSfx, _
((Abs(N) Mod 10) * 2) - 1, 2)
End If

End Function


Then enter this formula in A1(or any other column in row 1) of "another
worksheet"

=INDIRECT(ordinalnumber(ROW()) & "!$F$21")

Copy down column A


Gord Dibben MS Excel MVP