hyperlink using today() compared against a row of dates

[fragelracken]

Excel Version: 2003

Issue: I'm trying to figure out how to hyperlink to a cell by looking up the current date and compare against a row of dates.

I would like to have cell C10 be a hyperlinked field that when clicked will lookup today's date, compare against the range D8:G8, locate the matching date and navigate to the appropriate cell in the same row.

Example, if Today is 12/2/2005 and I were to click cell C11 The cursor would be redirected to Cell E11.

Admin's CheckList-Draft.v1.9.4-Jeromy.xls
	B	C	D	E	F	G
8	#	Activity	12/1/2005	12/2/2005	12/3/2005	12/4/2005
9	1.00	Networking
10	1.01	Monitor Network Traffic
11	1.02	Verify Event Log is Clear of Critical Errors & Resolve Any Errors When Detected
12	1.03	Check for Updated Firmware for Firewalls & VPN Client Software
13	1.04
14	1.05
November-2005

Thank you very much for your time, trying to resolve this problem has been killing me and I've been working on it on and off for over 2 weeks.

Thank you,
Yosemite Sam

 

[nbrcrunch]

I have a similar function. Rather than spend time converting it, I'll tell you what I do and you can adapt it.

Setup
Top row of column C through M (C1:M1), I have dates

Cell A1 contains formula
=HYPERLINK(D29&D30,TODAY())

D29 contains the formula identifying the workbook and sheet:
=MID(CELL("filename"),FIND("[",CELL("filename"),1),LEN(CELL("filename")))&"!"

D30 contains a formula working out the cell reference
=TEXT(ADDRESS(2,MATCH(TODAY(),C1:M1,)+2),"")

What is displayed in A1, then, is the dynamic current date, hyperlinked.
when clicked on, it takes the user to the first input cell (row 2) under the correct date.

 

[fragelracken]

nbrcrunch, you are awesome! I am so grateful. I have taken your code and played with it a bit. At first I figured out I needed to save the new excel file I created in order for your code to work properly
=MID(CELL("filename"),FIND("[",CELL("filename"),1),LEN(CELL("filename")))&"!"

This bit requires the excel document to be saved so it can retrieve the workbook name and sheet name.

Next I discovered the second part: =TEXT(ADDRESS(2,MATCH(TODAY(),C1:M1,)+2),"") would require me to create one of these entries for each reference in column B which wasn't too appealing for me so I decided to try and make a single reference to your code as:
=HYPERLINK(MID(CELL("filename"),FIND("[",CELL("filename"),1),LEN(CELL("filename")))&"!"&TEXT(ADDRESS(2,MATCH(TODAY(),C1:G1,)+2),""),"Monitor Network Traffic")

Once I did this I realized it would be best to merge this part into my cell
=TEXT(ADDRESS(2,MATCH(TODAY(),C1:M1,)+2),"")

but leave the other part as a separate cell which would be referenced
=MID(CELL("filename"),FIND("[",CELL("filename"),1),LEN(CELL("filename")))&"!"

This is what I ended up with and it actually works!!!

Thanks a million! (Note, I decided to hide cell I1 so it keeps my document looking clean.

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K
1	#	Acivity	12/1/2005	12/2/2005	12/3/2005	12/4/2005	12/5/2005
2	1.01	Monitor Network Traffic
3	1.02	Verify Event Log is Clear of Critical Errors
4	1.03	Check for Updated Firmware for Firewalls
5
6	1.01	=HYPERLINK(MID(CELL("filename"),FIND("[",CELL("filename"),1),LEN(CELL("filename")))&"!"&TEXT(ADDRESS(2,MATCH(TODAY(),C1:G1,)+2),""),"Monitor Network Traffic")
7	1.02	=HYPERLINK(I1&TEXT(ADDRESS(3,MATCH(TODAY(),C1:G1,)+2),""),"Verify Event Log is Clear of Critical Errors")
8	1.03	=HYPERLINK(I1&TEXT(ADDRESS(4,MATCH(TODAY(),C1:G1,)+2),""),"Check for Updated Firmware for Firewalls")
9
10	Cell I1	=MID(CELL("filename"),FIND("[",CELL("filename"),1),LEN(CELL("filename")))&"!"
11	Disp. As	[Book1.xls]Sheet1!
12	Hide IT!	To Hide the value created with '=MID(CELL("filename"),FIND("[",CELL("filename"),1),LEN(CELL("filename")))&"!"
13		1. On the Format menu, click Cells.
14		2. click Custom in the Category list, select the existing codes in the Type box,
15		and then type ;;; (three semicolons) in the Type box.
Sheet1

 

[fragelracken]

Discovered Issue using this Hyperlinking Method...

As I started getting involved in this method of hyperlinking I noticed an issue I was hoping someone could help me with.

Issue - If I create multiple tabs, one for each month... Dec_2005, Jan_2006, Feb_2006, etc. using the variable

=HYPERLINK(MID(CELL("filename"),FIND("[",CELL("filename"),1),LEN(CELL("filename")))&"!"&TEXT(ADDRESS(10,MATCH(TODAY(),I8:M8,)+8),""),"Monitor Network Traffic")

For the tabs where a date that matches today() is not found in the array, ALL values become N/A.

I really need all values to remain available even if the date referencing doesn't match any dates in the array. I don't know the answer and I know I could somehow incorporate an If than else formula; however I would prefer to keep the code short and not duplicate text strings if I don't have to.

I really like this code; however, unless someone has an answer to letting text fields display even if the current date doesn't match the dates in the array, I'm stuck.

Book3.xls
	B	C	D	E	F	G
7		[Book3.xls]Jan!
8	#	Activity	Admin	1/4/2006	1/5/2006	1/6/2006
9	1.00	Networking
10	1.01	#N/A
11	1.02	#N/A
12	1.03	#N/A
13	1.04
14	1.05	=HYPERLINK(C7&TEXT(ADDRESS(45,MATCH(TODAY(),I8:AM8,)+8),""),"Monitor Network Traffic")
Jan

 

[nbrcrunch]

#N/A can be captured using an IF statement along with ISERROR, just as you suggest. As far as not repeating the formula for the sake of the IF and keeping the syntax short, I can only tell you to take courage, Excel version 12 addresses that with a new function.

Scroll just a less than half way down this link:
http://blogs.msdn.com/excel/archive/2005/10.aspx

Until then, you could name your formula and then combine it in an if statement.

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/named_formulas.html

 

[fragelracken]

One final question on formula...

Thank you nbrcrunch. Using this formula worked...

=IF(ISERROR(HYPERLINK(I1&TEXT(ADDRESS(4,MATCH(TODAY(),C1:H1,)+2),""),"Check for Updated Firmware for Firewalls")),"Check for Updated Firmware for Firewalls")

I will look forward to the update in Office 12.

One last question if you don't mind which relates to the original formulas you provided...

If I use this code

=MID(CELL("filename"),FIND("[",CELL("filename"),1),LEN(CELL("filename")))&"!"

as a reference in each tab of the workbook (Say I have 12 tabs, Jan - Dec, the 1st time I click a hyperlink it sets the variable to the workbook/sheet I'm currently working in; however, If I move to a new tab/sheet and attempt to click another hyperlink, the field that contain the code has been set the workbook/sheet of the source tab when the hyperlink was 1st clicked.
Say I'm in Sheet 1 and click a hyperlink it sets this value:

[Book1.xls]Sheet1!

Now If I go to sheet2 the value for the code has been set to this value again

[Book1.xls]Sheet1!

instead of

[Book1.xls]Sheet2!

Now if I click the cell where the code is to edit the text, than hit enter it will refresh to that sheet and reset all tabs in the workbook to the new value.

Is there any way to have this variable auto-refresh without needing to edit and enter for each time I switch between tabs?

You have been most helpful, thank you for all your assistance.

 

[nbrcrunch]

It appears your needs are more extensive than mine. Sorry, I don't have time to research that for you.

 

[fragelracken]

Just thought I would post this update... This is my final product...

I need to create these types of notes so 6 months from now I can remember how to edit the formula. I hope this helps someone else...

Book4.xls
	A	B	C	D	E	F	G	H	I	J	K	L
39	#	Acivity	12/1/2005	12/2/2005	12/3/2005	12/4/2005	12/5/2005	12/6/2005	Replace the cell to the left with forumla =today()
40	1.01	Monitor Network Traffic							and watch the MAGIC!!!
41	1.02	Verify Event Log is Clear of Critical Errors
42	1.03	Check for Updated Firmware for Firewalls
43
44	1.01	=IF(ISERROR(MATCH(TODAY(),$C$39:$H$39,)+2),"Invalid",HYPERLINK($I$1&TEXT(ADDRESS(40,MATCH(TODAY(),$C$39:$H$39,)+2),""),"Monitor Network Traffic"))
45	1.02	=IF(ISERROR(MATCH(TODAY(),$C$39:$H$39,)+2),"Invalid",HYPERLINK($I$1&TEXT(ADDRESS(41,MATCH(TODAY(),$C$39:$H$39,)+2),""),"Verify Event Log is Clear of Critical Errors"))
46	1.03	=IF(ISERROR(MATCH(TODAY(),$C$39:$H$39,)+2),"Invalid",HYPERLINK($I$1&TEXT(ADDRESS(42,MATCH(TODAY(),$C$39:$H$39,)+2),""),"Check for Updated Firmware for Firewalls"))
47
48		=IF(ISERROR(MATCH(TODAY(),$C$39:$H$39,)+2),"Invalid",HYPERLINK($I$1&TEXT(ADDRESS(40,MATCH(TODAY(),$C$39:$H$39,)+2),""),"Monitor Network Traffic"))
49		=IF(ISERROR = Looking for an Error in the query (If the date today() is not in the array an error will be returned and we want to change the error to the name of the task
50		(MATCH(TODAY(),$C$39:$H$39,)+2) --- Looking in the array C39-H39 for the date today(), +2 says the array starts after 2 rows on the left (A and B) the array starts at C
51		"Invalid" --- This is the Text that's displayed if the date is NOT in the Array. Please change to the appropriate task name
52		HYPERLINK --- Is going to make this field a hyperlink
53		($I$1&TEXT(ADDRESS --- Is joining Cell I1 (this is a formula to display the Workbook and Sheet# which is required for hyperlinking) with &text(address string
54		40 --- the Row number where to place the hyperlink, MACTH(TODAY(),$C$39:$H$39,)+2),"") --- looking for th ematch today() in array C39-H39 starting at column 3 or C.
Formulas

nbrcurnch, without your help I wouild still be stuck, thank you for all your assistance. Now I can have a Merry Christmas... Happy Holidays!