Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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, |
#4
|
|||
|
|||
"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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy with link between sheets - all the formats dissapear?!? | Excel Discussion (Misc queries) | |||
how do I make a copy of a worksheet and retain formulas but not data | Setting up and Configuration of Excel | |||
Past Link then copy | Excel Discussion (Misc queries) | |||
How do link to a remote worksheet using the path value in a field? | Links and Linking in Excel | |||
Copy worksheet with Pivot Table and break link to original workshe | Excel Worksheet Functions |