Links to multiple Worksheets

[Paul Sheppard]

I have a workbook (Excel2000) with 130+ worksheets, I want to create a
Master worksheet with a link to cell A1 of each worksheet in column A
of the master worksheet

eg. in the the master cell A1 is linked to cell A1 Sheet1, A2 is linked
to cell A1 Sheet2 etc

Is there a quick way to do this or do I have to create each link
separately

Thanks

Paul

 

[Bryan Hessey]

=INDIRECT("sheet"&ROW()&"!A1")
and formula-drag down the column for 130 rows

 

[Ron de Bruin]

Hi Paul

Run this macro one time
http://www.rondebruin.nl/summary.htm

 

[Paul Sheppard]

=INDIRECT("sheet"&ROW()&"!A1")
and formula-drag down the column for 130 rows

Brian

Thanks for this, this only seems to work if the worksheets are called
sheet1 sheet2 etc, how does this need to change if the sheets have
different names?

 

[Ron de Bruin]

See my code example Paul

 

[Paul Sheppard]

Hi Paul

Run this macro one time
http://www.rondebruin.nl/summary.htm


Ron

Thanks for this, it works ok if I use it as a macro within th
workbook, so have solved my current problem

I have since copied the macro to personal.xls so that I could use i
again with other workbooks, the macro runs ok but i cannot see th
Summary Sheet when I run it this way, when I run it again on a ne
workbook I get a message saying the summary sheet already exists i
this workbook, but I cannot see it and it is not a hidden worksheet

Any help greatfully received

Pau

 

[Ron de Bruin]

Hi Paul

Change

Set Basebook = ThisWorkbook

To

Set Basebook = ActiveWorkbook

 

[Paul Sheppard]

Thanks Again Ron

Pau

 

[Bryan Hessey]

My next suggestion would have been a Command Button (or Macro) assuming
3 rows of header before the index, and showing the Sheet number in A4
onwards, the Sheetname in B4 onwards, and the contents of that sheet's
A1 in C4 onwards - as:

Private Sub CommandButton1_Click()
For I = 1 To Worksheets.Count
Range("a" & I + 3) = I
Range("b" & I + 3) = Sheets(I).Name
Next
End Sub

then in cell c4 put
=INDIRECT(B4&"!A1")

but this does not appear to work with sheetnames that contain spaces,
so many thanks to Ron for a better solution (and I'll work on the
spaces).


Also, did you receive an answer to your earlier question :" is it
possible to split it to show John / A / Doe in 3 separate cells"

in H29 =LEFT(F27,FIND(" ",F27&" "))
in I30 =IF(LEN(F27)>LEN(H28),TRIM(MID(F27,FIND("
",F27),(LEN(F27)-LEN(H29)-LEN(J31)))),"")
in J31 Bob's =RIGHT(F27,LEN(F27)-FIND("^^",SUBSTITUTE(F27,"
","^^",LEN(F27)-LEN(SUBSTITUTE(F27," ","")))))

will split out the first word, the last word, and any remaining words
from F27 (I know A1 is usually used, but I'd rather copy the current
working version) with the proviso that the original cell had a space
(presumably 'Superman' and 'Madona' won't appear in your list)

Note, if you are sorting these names by lastname there could be a need
for a fourth column to extract words such as 'de'. 'du' and 'van' so
that Vincent sorts into the V area with the other Van Goghs rather that
sorting into the G area.

To explain the second cell, start at the first space, for a length of
original length less the lengths already extracted in cell one and
three, (ie, to the last space), then Trim to remove any first and last
space. It's lazy but easy to count.

 

[Paul Sheppard]

Brian

Thanks for the solution to splitting the middle name

Pau

 

[gwexcel]

That is a great macro, very useful for my application.

My question: Is there a way to apply a sheet format to the macro? Th
first 6 lines of my sheets contain company info, date, job# etc. I
there a way to have the macro input this info?

thanks