Excel help Sheet Name...linking into Spreadsheet

[Mike]

Hi all

Quick Excel question !

I have a spreadsheet with lots of named pages...ie tabs at the bottom. Can I link these into the actaul spreadsheet rather then typing them all in or setting headers. Ie in a cell sort of : A1 = sheet name.

Any ideas

Thanks

Mike

[ikthius]

I done a search for linking worksheets to cells in excel, and it gave me this:

Examples

The following example opens a worksheet named Budget Report.xls that is stored on the Internet at the location named example.microsoft.com/report and displays the text "Click for report":

=HYPERLINK("http://example.microsoft.com/report/budget report.xls", "Click for report")

The following example creates a hyperlink to cell F10 on the worksheet named Annual in the workbook Budget Report.xls, which is stored on the Internet at the location named example.microsoft.com/report. The cell on the worksheet that contains the hyperlink displays the contents of cell D1 as the jump text:

=HYPERLINK("[http://example.microsoft.com/report/budget report.xls]Annual!F10", D1)

The following example creates a hyperlink to the range named DeptTotal on the worksheet named First Quarter in the workbook Budget Report.xls, which is stored on the Internet at the location named example.microsoft.com/report. The cell on the worksheet that contains the hyperlink displays the text "Click to see First Quarter Department Total":

=HYPERLINK("[http://example.microsoft.com/report/budget report.xls]First Quarter!DeptTotal", "Click to see First Quarter Department Total")

although you don't have to use an internet domain name, you could just navigate to the file you want, and it should find the file, and get the required data.


ik

EDIT: or this:
create a link between cells in the same worksheet or workbook

1. Click the cell that contains the data you want to link to, and then click Copy [img]../images/0019.gif[/img] on the Standard toolbar.
2. Click the cell you want to link from, and then click Paste [img]Download Failed (1)[/img].
3. Click Paste Options [img]Download Failed (1)[/img] and then click Link Cells.

[img]../images/bluedrop.gif[/img]Create a link between cells in different workbooks

1. Open both the workbook that will contain the link (called the destination workbook), and the workbook that contains the data you want to link to (called the source workbook).
2. In the destination workbook, click Save [img]../images/0003.gif[/img].
3. Select a cell or cells you want to link from.
4. If you are creating a new formula, type = (an equal sign). If you are entering the link elsewhere in the formula, type the operator or function that you want to precede the link.
5. On the Window menu, click the name of the source workbook, and then click the worksheet that contains the cells you want to link to.
6. Select the cells you want to link to.
7. Complete the formula. When you finish entering the formula, press ENTER.

Or:
if on the same workbook
=sheet2!B2 (uses value in cell B2 on worksheet 2)

[andygrif]

As Ikthius said you can create hyperlinks to a complete sheet - but I am wondering if you want to link to a CELL in a different sheet. If so it's as easy as putting your forumula in the cell and then (instead of clicking to get the value of a cell on that sheet) click the tab of the sheet you want, then the cell in that sheet, and press enter.

[Mike]

I have a spreadsheet with about 30 named sheets and I want to copy a formula on each sheet to 'pick up' the sheet name. Hope that makes sense.

Thanks

Mike

[gazzae]

Use this forumula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34)

Basically the =(cell("filename")) function returns - c:\data\[book1.xls]sheetname

The formula above returns whatever is after the ] ie the sheetname.

[Mike]

Originally Posted by gazzae

Use this forumula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34)

Basically the =(cell("filename")) function returns - c:\data\[book1.xls]sheetname

The formula above returns whatever is after the ] ie the sheetname.

Thanks for this but just comes up with #Value!

Mike

[gazzae]

Originally Posted by Mike

Thanks for this but just comes up with #Value!

Mike

The sheet has to be saved for the "filename" formula to work. If its not saved you will get the #Value!

[Mike]

Originally Posted by gazzae

The sheet has to be saved for the "filename" formula to work. If its not saved you will get the #Value!

Gazzae

Worked many thanks for everyone help

[Nemesis]

If you created a Module and add this code to it, the sheet names will appear in the first column on Sheet1.

Function SheetNames() As Variant
Dim SheetN As String
Dim SheetN2() As Variant
Dim SheetCount As Integer
Dim x As Integer

Application.Volatile
x = 1
SheetCount = Worksheets.Count
Worksheets(1).Range("A:A").Clear
For x = 1 To SheetCount
With Worksheets
SheetN = .Item(x).Name
End With
ReDim Preserve SheetN2(SheetCount)
SheetN2(x) = SheetN
Worksheets(1).Cells(x, 1).Value = SheetN2(x)
Next
End Function

Sub SheetName()
Call SheetNames
End Sub

It also creates a Macro called Sheetname that can be run to update the list if any are added or taken away.

It's basic, but does the job.

[Nemesis]

Changed it Slightly to be a bit more generic

[Mike]

Originally Posted by Simon M

If you created a Module and add this code to it, the sheet names will appear in the first column on Sheet1.



It also creates a Macro called Sheetname that can be run to update the list if any are added or taken away.

It's basic, but does the job.

Looks good but how do you create a Module

thanks Mike

[Nemesis]

Tools ... Macro ... Visual basic Editor .... Insert ... Module

Paste the code in, save ... and away you go

[gazzae]

Originally Posted by Simon M

If you created a Module and add this code to it, the sheet names will appear in the first column on Sheet1.



It also creates a Macro called Sheetname that can be run to update the list if any are added or taken away.

It's basic, but does the job.

Complicated!

This does the same..

For i = 1 To Sheets.Count
Worksheets(1).Cells(i, 1) = Worksheets(i).Name
Next i

[Nemesis]

Originally Posted by gazzae

Complicated!

This does the same..

Yep, I was trying to do some other stuff with the sheet names, and so needed them in variables, but your's does the the basic listing without needing the variables.

[Nemesis]

k, with a little more playing ...

Create a Module, as per above.

Put this code in.

Function SheetName() As Variant
For i = 1 To Worksheets.Count
Worksheets(i).Range("A1:A1").Clear
Worksheets(i).Cells(1, 1).Value = Worksheets.Item(i).Name
Next
End Function


Sub NameSheets()
Call SheetName
End Sub

Double click on "ThisWorksheet" and add this code.

Private Sub Workbook_Open()
Call SheetName
End Sub

This will automatically run the function when the Workbook opens and place the name of the sheet in Cell A1 on each sheet.

In addition there is a Macro which can be used when things have been changed, added or removed.