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


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392061

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



Paul Sheppard Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=392061

Hi Paul

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Paul Sheppard" wrote in message
news:Paul.Sheppard.1t4kqt_1122966326.4131@excelfor um-nospam.com...

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


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392061

Bryan Hessey Wrote:
=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?


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392061

See my code example Paul

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Paul Sheppard" wrote in message
news:Paul.Sheppard.1t56yj_1122995111.0628@excelfor um-nospam.com...

Bryan Hessey Wrote:
=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?


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392061

Ron de Bruin Wrote:
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 the
workbook, so have solved my current problem

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

Any help greatfully received

Paul


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392061

Hi Paul

Change

Set Basebook = ThisWorkbook

To

Set Basebook = ActiveWorkbook


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Paul Sheppard" wrote in message
news:Paul.Sheppard.1t59r4_1122998748.9864@excelfor um-nospam.com...

Ron de Bruin Wrote:
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 the
workbook, so have solved my current problem

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

Any help greatfully received

Paul


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392061

Thanks Again Ron

Paul


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392061

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 Wrote:


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 Again Ron

Paul


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=392061

Brian

Thanks for the solution to splitting the middle name

Paul


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392061

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

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

thanks,


--
gwexcel
------------------------------------------------------------------------
gwexcel's Profile: http://www.excelforum.com/member.php...fo&userid=6845
View this thread: http://www.excelforum.com/showthread...hreadid=392061