If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Dynamic" hyperlink
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 |
#2
|
|||
|
|||
"Dynamic" hyperlink
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: |
#3
|
|||
|
|||
"Dynamic" hyperlink
Sorry, it actually returns #Name? after I Changed , for ;
Returned nothing before I did those changes |
#4
|
|||
|
|||
"Dynamic" hyperlink
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 --------------- Jacob Skaria "Espen Rostad" wrote: 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 |
#5
|
|||
|
|||
"Dynamic" hyperlink
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 --------------- Jacob Skaria "Espen Rostad" wrote: Sorry, it actually returns #Name? after I Changed , for ; Returned nothing before I did those changes |
#6
|
|||
|
|||
"Dynamic" hyperlink
'to return the address of A1
=Cell("address",A1) - returns $A$1 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: |
#7
|
|||
|
|||
"Dynamic" hyperlink
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,"mmddy yyy")&"!A1"))),"", HYPERLINK("#" & CELL("address",INDIRECT(TEXT(A1,"mmddyyyy")&"!A1") ),"Click me")) If this post helps click Yes --------------- Jacob Skaria "Espen Rostad" wrote: 'to return the address of A1 =Cell("address",A1) - returns $A$1 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: |
#8
|
|||
|
|||
"Dynamic" hyperlink
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: |
#9
|
|||
|
|||
"Dynamic" hyperlink
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 --------------- Jacob Skaria "Espen Rostad" wrote: 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: |
Thread Tools | |
Display Modes | |
|
|