Excel help Sheet Name...linking into Spreadsheet
 

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

Re: Excel help Sheet Name...linking into Spreadsheet
 

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)

Re: Excel help Sheet Name...linking into Spreadsheet
 

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.

Re: Excel help Sheet Name...linking into Spreadsheet
 

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

Re: Excel help Sheet Name...linking into Spreadsheet
 

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.

Re: Excel help Sheet Name...linking into Spreadsheet
 

Thanks for this but just comes up with #Value!

Mike

Re: Excel help Sheet Name...linking into Spreadsheet
 

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

Re: Excel help Sheet Name...linking into Spreadsheet
 

Gazzae

Worked many thanks for everyone help :tu: :tu:

Re: Excel help Sheet Name...linking into Spreadsheet
 

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.

Re: Excel help Sheet Name...linking into Spreadsheet
 

Changed it Slightly to be a bit more generic

Re: Excel help Sheet Name...linking into Spreadsheet
 

Looks good but how do you create a Module

thanks Mike

Re: Excel help Sheet Name...linking into Spreadsheet
 

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

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

Re: Excel help Sheet Name...linking into Spreadsheet
 

Complicated! :)

This does the same..

Re: Excel help Sheet Name...linking into Spreadsheet
 

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.

Re: Excel help Sheet Name...linking into Spreadsheet
 

k, with a little more playing ...

Create a Module, as per above.

Put this code in.

Double click on "ThisWorksheet" and add this code.
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.