# Copying worksheet cells into another worksheet using autofill

S

#### 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!

S

#### 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...

G

#### Gary''s Student

Assuming Sheet1 thru Sheet31, use something like:

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

G

#### 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