workbook hyperlink code (Excel 2007)

[steveh]

Morning all

could somebody help me with a piece of temporary hyperlink code please

I have 100 sheet tabs Emp 1 : Emp 100

On an index sheet I would like a column that show the names with a hyperlink to cell C3 in each sheet, with [URL=http://www.asap-utilities.com/]ASAP Utilities[\URL] I can create the hyperlinks but the curved ball is that I would like each cell to have =emp 1!C3, =emp 2!C3 etc. as well as the hyperlink

TIA

[HansV]

Let's say the first hyperlink should be in row 1:

=HYPERLINK("[Book1.xlsx]Emp "&ROW()&"!C1")

Here, Book1.xlsx is the name of the workbook.
If the first hyperlink (to Emp 1) should be in row 2:

=HYPERLINK("[Book1.xlsx]Emp "&ROW()-1&"!C1")

The formula can be filled or copied down.

[steveh]

Let's say the first hyperlink should be in row 1:

=HYPERLINK("[Book1.xlsx]Emp "&ROW()&"!C1")

Here, Book1.xlsx is the name of the workbook.
If the first hyperlink (to Emp 1) should be in row 2:

=HYPERLINK("[Book1.xlsx]Emp "&ROW()-1&"!C1")

The formula can be filled or copied down.

Hi Hans

Thanks for the quick response, so I have changed the formula to:-

=HYPERLINK("[HolidayPlanner_v1.2.xlsx]Emp "&ROW()-1&"!C3") - this shows in the formula bar
[HolidayPlanner_v1.2.xlsx]Emp 1!C3 - this shows in the cell

But when I click it the dialog says 'reference is not valid', I should imagine I have done something wrong but can you see what?

[HansV]

Try this instead:

Code: Select all

=HYPERLINK("[HolidayPlanner_v1.2.xlsx]'Emp "&ROW()-1&"'!C3")

[steveh]

Try this instead:

Code: Select all

=HYPERLINK("[HolidayPlanner_v1.2.xlsx]'Emp "&ROW()-1&"'!C3")

Thanks Hans