Getting value of Cell A1 on successive sheets onto Sheet 1

[McAllan]

I have a workbook with >90 sheets, all with a unique name (not sequential). I have the sheet name in cell A1 using (=RIGHT(CELL("filename",A1),(LEN(CELL("filename",A1)))-(FIND("]",CELL("filename",A1))))) on every sheet. I want to create hyperlinks from a list on the first sheet without having to go to each successive sheet to get the contents of cell A1. This is obviously a very laborious operation.
Firstly, how do I populate the list in column A with the contents of successive sheets on successive rows?
Secondly, is it possible to create a hyperlink using the contents of the list that will be in column A?
The contents of cell A5 on the list is ='0831 25 mm Sq'!A1
The contents of cell A6 on the list is ='0450 Rx'!A1<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Many thanks.

 

[Ruddles]

This code will create a set of hyperlinks in whichever sheet you set as sMenuSheet starting from cell A1. Place it in a general code module and run it. (I suggest the first time you try it out, you start with a completely new, empty workbook.)

Option Explicit
 
Public Sub CreateLinks()
 
  Const [B]sMenuSheet[/B] As String = "[COLOR=red][B]Sheet1[/B][/COLOR]"
  Dim ws As Worksheet
  Dim iRow As Long
  Dim hCell As Range
 
  iRow = 0
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> sMenuSheet Then
      iRow = iRow + 1
      Set hCell = ThisWorkbook.Sheets(sMenuSheet).Cells(iRow, 1)
      ThisWorkbook.Sheets(sMenuSheet).Hyperlinks.Add Anchor:=hCell, Address:="", _
          SubAddress:=ws.Name & "!A1", TextToDisplay:="Go to " & ws.Name

    End If
  Next ws
 
End Sub

I don't quite understand your 'secondly'. Are you saying that you want each of the 90+ sheets to have a 'return to sheet 1' hyperlink?

If so, insert the following statement immediately before the End If line:-

      ws.Hyperlinks.Add Anchor:=ws.Range("[COLOR=blue][B]A1[/B][/COLOR]"), Address:="", _
          SubAddress:="Sheet1!A1", TextToDisplay:="Back to Sheet1"

This will place a return hyperlink in cell A1 of each of the sheets. Modify as reauired.

 

[Ruddles]

"required".

 

[T. Valko]

I have a workbook with >90 sheets, all with a unique name (not sequential). I have the sheet name in cell A1 using (=RIGHT(CELL("filename",A1),(LEN(CELL("filename",A1)))-(FIND("]",CELL("filename",A1))))) on every sheet. I want to create hyperlinks from a list on the first sheet without having to go to each successive sheet to get the contents of cell A1. This is obviously a very laborious operation.
Firstly, how do I populate the list in column A with the contents of successive sheets on successive rows?
Secondly, is it possible to create a hyperlink using the contents of the list that will be in column A?
The contents of cell A5 on the list is ='0831 25 mm Sq'!A1
The contents of cell A6 on the list is ='0450 Rx'!A1<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Many thanks.

Just for the heck of it...

Here's a formula method to create the hyperlinks.


Create this defined named formula:

• SheetNames
• Refers to: =GET.WORKBOOK(1)&T(NOW())
• OK

On the sheet where you want the hyperlinks, enter this formula in cell A1:

=HYPERLINK("#"&INDEX(MID(SheetNames,FIND("]",SheetNames)+1,31),ROWS(A$1:A1))&"!A1",INDEX(MID(SheetNames,FIND("]",SheetNames)+1,31),ROWS(A$1:A1)))

Copy down until you get errors (meaning all the sheet names have been extracted). Delete those cells that contain the errors.

That will create hyperlinks to each sheet cell A1.

 

[LearnMeExcel]

Biff
how i can understand this kind of Excel formula (macro4)

GET.WORKBOOK

 

[T. Valko]

Biff
how i can understand this kind of Excel formula (macro4)

GET.WORKBOOK

The best way would be to get the help file.

You can download the macro function help file here:

http://support.microsoft.com/kb/128185

Download the *.exe file

Run the file to extract the help files

It will extract the following files:
Macrofun.cnt
Macrofun.GID
Macrofun.hlp

Double click the Macrofun.hlp file