copy link from next worksheet

[mate]

I have a wookbook containing approx 65 separate sheets. First sheet is a
summary page which i need to copy a value from each separate sheet. I can't
copy these using a fill series, and to do an individual link from each sheet
would take a while, so my question:
Is there a quick way to copy a link and fill down (where down means moving
to next worksheet).
ie.
summary sheet a1 = worksheet 1 cell b1
summary sheet a2 = worksheet 2 cell b1
summary sheet a3 = worksheet 3 cell b1

Many thanks in advance,

 

[RagDyeR]

Try this:

=INDIRECT("Sheet"&ROW(A1)&"!B1")

If sheets are default XL names.

If the names are exactly as in your example, try this:

=INDIRECT("'workSheet "&ROW(A1)&"'!B1")

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I have a wookbook containing approx 65 separate sheets. First sheet is a
summary page which i need to copy a value from each separate sheet. I can't
copy these using a fill series, and to do an individual link from each sheet
would take a while, so my question:
Is there a quick way to copy a link and fill down (where down means moving
to next worksheet).
ie.
summary sheet a1 = worksheet 1 cell b1
summary sheet a2 = worksheet 2 cell b1
summary sheet a3 = worksheet 3 cell b1

Many thanks in advance,

 

[Gord Dibben]

mate

How are your worksheets named?

Sheet1, Sheet2 etc. or unique names?

Makes a great difference in what approach(formula) to take.


Gord Dibben Excel MVP

 

[mate]

mate

How are your worksheets named?

Sheet1, Sheet2 etc. or unique names?

Makes a great difference in what approach(formula) to take.

The worksheets are actually names eg. smith, jones, thomas etc - these can't
be altered to anything other than names unfortunately.

i re-read my post and not sure if it was very clear, what i want to achieve
is:
on summary sheet, cell A1 = link to value in B1 on sheet 'jones'
on summary sheet, cell b1 = link to value in B1 on sheet 'smith'

Is there a quick way to do this? Or should i just link manually?

Thanks again.

Jan

 

[Ragdyer]

Do you have, or can you create, a list of those names?

If, for example, the list was in A1:A65, enter this in B1:

=INDIRECT(A1&"!B1")

And copy down,
Or *double* click the "fill handle" of B1, which will *automatically* copy
the formula in B1 down Column B, as far as there is data in Column A.

 

[Gord Dibben]

Jan

RD assumed you wanted the Summary sheet data to run down a column.

Your post indicates across row 1.

With Summary sheet first in workbook and selected....to get sheet names across
row 1 of a new sheet named "List" run this macro.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
With Sheets
.Add.Name = "List"
End With
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> "List" And Sheet.Name <> "Summary" Then
Rng.Offset(0, i).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub

Now the formula in A1 of Summary sheet would read =INDIRECT(List!A1&"!B1")

Copy/drag across 65 columns.


Gord

 

[Ragdyer]

Hey Gord,

OP states *down* Column A!

But you're correct, when referring to the *second* post!
Am I supposed to *completely* read them both?<bg>

Anyway, lets have the list of sheet names start in A10, and continue down to
A74.

Enter this formula in A1:

=INDIRECT(OFFSET($A$10,COLUMN(A1)-1,)&"!B1")

And drag across the 65 columns as needed to BM1.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Jan

RD assumed you wanted the Summary sheet data to run down a column.

Your post indicates across row 1.

With Summary sheet first in workbook and selected....to get sheet names across
row 1 of a new sheet named "List" run this macro.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
With Sheets
.Add.Name = "List"
End With
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> "List" And Sheet.Name <> "Summary" Then
Rng.Offset(0, i).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub

Now the formula in A1 of Summary sheet would read =INDIRECT(List!A1&"!B1")

Copy/drag across 65 columns.


Gord

 

[Gord Dibben]

RD

Sorry about that, I didn't re-read the original post which does say down
column A.

Second post goes across row 1.

Sometimes hard to keep up<g>


Gord