Hyperlink to another sheet based on Today()

[jblevins]

Trying to create a dynamic hyperlink based on today's date to a cell in another sheet which has the abbreviated name of the month (ex. Jan). This does not work:

=HYPERLINK(TEXT(Q3,"mmm")&"!"&"B2","test")

 

[jasonb75]

I couldn't remember exactly how it would need to be written so I looked at excel's built in help file to see what it said about the HYPERLINK function and found this

Support page

=HYPERLINK("[Book1.xlsx]January!A10","Go to January > A10")

To jump to a different location in the current worksheet, include both the workbook name, and worksheet name like this, where January is another worksheet in the workbook.

 

[jblevins]

I couldn't remember exactly how it would need to be written so I looked at excel's built in help file to see what it said about the HYPERLINK function and found this

Support page

=HYPERLINK("[Book1.xlsx]January!A10","Go to January > A10")

To jump to a different location in the current worksheet, include both the workbook name, and worksheet name like this, where January is another worksheet in the workbook.

You have the location in the example and I want the formula to calculate the month (sheet name) from the current day (Today() or Now() function).

 

[jasonb75]

If you read the information that I posted properly then you would have noticed that it says you need to include both the workbook name, and worksheet name.
The workbook name was not in the information that you supplied, it simply needs to be added to the formula in line with the example from the help file.

 

[jblevins]

I can add the workbook name, but I cannot add the worksheet name, it defeats the purpose in creating the link; that must be determined by the formula and if that can't be done, then the formula cannot be used.

 

[jasonb75]

but I cannot add the worksheet name

You have already added the worksheet name with the TEXT function, you just need to put the workbook name in front of it.

 

[jblevins]

Here is what I got and it still fails (Reference is not valid), had to remove the quotes around "mmm":

=HYPERLINK("[Forever Calendar.xls]TEXT(Today(),mmm)&!B2","test")

 

[jasonb75]

You were close

=HYPERLINK("[Forever Calendar.xls]"&TEXT(Today(),"mmm")&"!B2","test")

 

[jblevins]

That works, you are my master jasonb75.

Have a great day and God bless...

 

[Roy_Klein]

I know this post was long time ago, but just to add to the solution, you can replace the filename "[Forever Calendar.xls]" with just "#"