Hyperlink to future week depending upon rule.

[Emeng]

Hi all

I have a list of dates into the future in column A and a list of corresponding week numbers in column B.

I am able to click and go directly to today’s date using

=HYPERLINK("#a"&MATCH(TODAY(),A:A,0),"GO TO TODAY"). I would like to be able to go to the next 'Planning Week' depending upon which day of the week today is – if today is Mon to Thu I want to go to next week, if Fri to Sun, then to the following week. I have tried this formula =HYPERLINK("#b"&MATCH(IF(weekday<5,(WEEKNUM(TODAY())),(WEEKNUM(TODAY()+1)),A:A,0)),"GO TO NEXT WEEK") but no success. Any help much appreciated.

Regards

Mark

 

[Faseeh]

Hi Mark!!


I have reached this far, plz see this file:


http://dl.dropbox.com/u/60644346/Mark_Partial%20Solution.xlsx


Please explain how your hyperlink will work in case of weeks, can you explain with an example?


Regards,

Faseeh

 

[Emeng]

Hi Faseeh

Thanks for your interest.

The week number cells are merged and centred in my spreadsheet and link is to work in the same way as in your example. Click the link and be taken to the appropriate Weeknum cell.

Regards

Mark

 

[Faseeh]

Hi Emeng,


In the same worksheet that i have uploaded, enter this formula:

=HYPERLINK("#C"&MATCH(WEEKNUM(TODAY()),$C$4:$C$21,0)+4,"GO TO THIS WEEK")

..that is working here on my sheet so i hope it works for you as well.


Regards,

Faseeh

 

[Emeng]

Hi Faseeh

I see the change of approach. An elegant solution, thanks very much for your help.

Regards

Mark