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

Advertisements

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
 
Ad

Advertisements

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top