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
workbook hyperlink code (Excel 2007)
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
workbook hyperlink code (Excel 2007)
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78894
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: workbook hyperlink code (Excel 2007)
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.
=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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: workbook hyperlink code (Excel 2007)
Hi HansHansV wrote: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.
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?
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78894
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: workbook hyperlink code (Excel 2007)
Try this instead:
Code: Select all
=HYPERLINK("[HolidayPlanner_v1.2.xlsx]'Emp "&ROW()-1&"'!C3")
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: workbook hyperlink code (Excel 2007)
Thanks HansHansV wrote:Try this instead:
Code: Select all
=HYPERLINK("[HolidayPlanner_v1.2.xlsx]'Emp "&ROW()-1&"'!C3")
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin