I have an overview worksheet that features a list of dates; I want to hyperlink the dates to the another worksheet within the workbook that represents the month. I want to do this without changing the dates in the overview sheet. For example, I have 2014-05-23, 2014-05-24,2014-05-25 in my overview sheet and I have a worksheet titled May 2014. When I try to hyperlink the dates to the May worksheet it turns the dates to #### in the overview sheet. How can I stop that from happening?
Something like
with the date in A3 and we want to look in B7 of sheet May
=INDIRECT(TEXT(A3,"mmmm yyyy") & "!"&B7)
Does that make sense?
Last edited by ChemistB; 05-27-2014 at 02:46 PM.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I do not understand the formula. I tried it but it didn't work. Essentially, I want to be able to click a date in my overview sheet and it will jump to the worksheet corresponding to month of the date.
________________________________________________Originally Posted by ChemistB
I tried this: =INDIRECT(TEXT(A4,"yyyymmdd") & "!"&'May 2014 '!) but not working
Try something like this...
Data Range
This formula entered in B2 and copied down:
=HYPERLINK("#'"&TEXT(A2,"mmm yyyy")&"'!A1","Go")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
It now says that the reference is invalid but I do feel like I am closer to my goal.Please find attached my workbook and see sheet titled Walkley's Editorial Calendar. I want to link the dates to the corresponding worksheet that reflects the month.
The sheet tab name for sheet May 2014 has a trailing space:
\May 2014[space]/
When I removed that space:
\May 2014/
And used this formula in cell C4 on the Walkley's Editorial Calendar sheet:
=HYPERLINK("#'"&TEXT(A4,"mmm yyyy")&"'!A1","Go")
It worked as expected.
Why does it refuse to work when I enter the formula. I still get "Reference is not valid". What am I missing
I deleted the name of the sheet and rewrote May 2014 and now it works. When I add the worksheet June 2014 would it be the same formula? I ask because I am not sure what the formula arguments mean.
Last edited by excellearner1990; 05-27-2014 at 08:33 PM.
Good deal!
Yes, same formula. As long as the cell the formula is referencing contains a valid date and the sheet name matches, it should work.When I add the worksheet June 2014 would it be the same formula? I ask because I am not sure what the formula arguments mean.
Thank you so much. Upon closer inspection, I figured out that the "mmm" refers to the month. So for June I should put "mmmm" and it will read my worksheet name. Does this mean that for August and going I have to use "mmm" format?
=TEXT(A1,"mmm yyyy")
mmm is the format code for the SHORT month name:
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
mmmm is the format code for the LONG month name:
January
February
March
April
May
June
July
August
September
October
November
December
yyyy is the format code for the LONG year number:
2010
2011
2013
2014
A1 = 8/26/2014
=TEXT(A1,"mmm yyyy") = Aug 2014
=TEXT(A1,"mmmm yyyy") = August 2014
So, you want to make sure that all the monthly sheet names use the same format so you can use the same formula.
Thank you so much for your help. My issue is resolved.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks