"Dynamic" hyperlink

  • Thread starter Thread starter Espen Rostad
  • Start date Start date
E

Espen Rostad

I have to create a large wb with one sheet per day named actual dates. On the
front page i need a cell to put the desired sheet (by entering date) to go
to. I'm thinking a cell to enter date and a "Go to" button. Is this possible?

PS! I'm not familiar with macros
 
Hello again Jacob.

I'm afraid that did not work. I should probably infirm you that I use Excel
2003. I tried changing the ,'s in the formula with ;'s but it still does'nt
work. Cell A2 returns blank and nothing happens when i click it (There seems
to be a link judging by pointer changing format)

Jacob Skaria skrev:
 
Sorry, it actually returns #Name? after I Changed , for ;
Returned nothing before I did those changes
 
Try the below.

--In cell A1 enter the date (excel date/time format)
--In cell B1 enter the below formula.
--The sheet names are assumed to be in the format mmddyyyyy eg:
11112009,11122009,11132009.. Change the format to suit in the formula...
--A link will appear only if there is a valid sheet name in cell A1.


=IF(ISERROR(CELL("address",INDIRECT( "'" &
TEXT(A1,"mmddyyyy") & "'!A1"))),"",HYPERLINK("#" & CELL("address",
INDIRECT( "'" & TEXT(A1,"mmddyyyy") & "'!A1")),"Clickme"))

If this post helps click Yes
 
Just try the below and let me know where you are having issues..

'to return the address of A1
=Cell("address",A1)

Create a sheet named 11112009 and try
=Cell("address", INDIRECT(TEXT(TODAY(),"mmddyyyy") & "!A1")))

Try the below which should create a hyperlink. referring to the date sheet
=HYPERLINK("#" &
CELL("address",INDIRECT(TEXT(TODAY(),"mmddyyyy")&"!A1")),"Click me")

The eearlier formula handles the error and returns the link..If error it
returns blank

If this post helps click Yes
 
'to return the address of A1
=Cell("address",A1) - returns $A$1
Click to expand...

Create a sheet named 11112009 and try
=Cell("address", INDIRECT(TEXT(TODAY(),"mmddyyyy") & "!A1")))
- returns '[Book3]11112009'!$A$1

The last formula creates a link that sends med to sheet 11112009
Jacob Skaria skrev:
 
OK. Good. Now in cell A1 enter a date and replace the date mentioned in the
formula to that cell

=HYPERLINK("#" &
CELL("address",INDIRECT(TEXT(A1,"mmddyyyy")&"!A1")),"Click me")

If the above works then try

=IF(ISERROR(CELL("address",INDIRECT(TEXT(A1,"mmddyyyy")&"!A1"))),"",
HYPERLINK("#" &
CELL("address",INDIRECT(TEXT(A1,"mmddyyyy")&"!A1")),"Click me"))

If this post helps click Yes
---------------
Jacob Skaria


Espen Rostad said:
'to return the address of A1
=Cell("address",A1) - returns $A$1
Click to expand...

Create a sheet named 11112009 and try
=Cell("address", INDIRECT(TEXT(TODAY(),"mmddyyyy") & "!A1")))
- returns '[Book3]11112009'!$A$1

The last formula creates a link that sends med to sheet 11112009
Jacob Skaria skrev:
Click to expand...
 
Fantastic :) They both work just fine! What's the difference, and which
should I use?

Thank you so much, you are my personal oracle ;)

Jacob Skaria skrev:
 
<<On the front page i need a cell to put the desired sheet (by entering date)
to go
<<to. I'm thinking a cell to enter date and a "Go to" button. Is this possi

The error handled one..(and no need to have a 'Goto' button) instead you
change the text displayed as 'Go to' instead of 'Click me'

If this post helps click Yes
 
Back
Top