copy link from next worksheet
 

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,

--
Jan

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


"mate" wrote in message
...
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,

--
Jan

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

On Sat, 5 Mar 2005 16:22:25 -0000, "mate" wrote:

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" <gorddibbATshawDOTca wrote in message
...
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

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.
--
HTH,

RD

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

"mate" wrote in message
...

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

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

On Sun, 6 Mar 2005 12:53:59 -0800, "Ragdyer" wrote:

o 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.
--
HTH,

RD

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

"mate" wrote in message
...

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

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




"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Sun, 6 Mar 2005 12:53:59 -0800, "Ragdyer"
wrote:

o 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.
--
HTH,

RD


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

---------------------------------------------------------------------------

"mate" wrote in message
...

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

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

On Sun, 6 Mar 2005 18:54:56 -0800, "Ragdyer" wrote:

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