Can I avoid Hyperlink formulas with #VALUE errors?

Hi Everyone,

I have a timesheet that I created which has 31 tabs (labeled 1 through 31) and a Summary tab. The Summary tab displays the current month as a calendar and sums up the month's activity in various ways. On the Summary tab, I use hyperlinks to take the user to the corresponding tabs for each date.

One of the formulas I use takes the user to today's tab if the month's header is today's month (and year). MonthHeader is a named range.

=IF(AND(MONTH(TODAY())=MONTH(MonthHeader),YEAR(TODAY())=YEAR(MonthHeader)),HYPERLINK(MID(CELL("filename"),SEARCH("[",CELL("filename")),SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))+1)&DAY(TODAY())&"!C4","To Today's Timesheet"),"")

I get this problem which appears where all of my Hyperlink formulas go to #VALUE errors and I haven't figured out all of the ways to replicate my problem but one way is to create another workbook and create a hyperlink formula and then click on it. For example I used =HYPERLINK("https://www.google.com"). When I go back to the timesheet workbook, all of my hyperlink formulas are #VALUE errors. I don't believe this error is specific to my timesheet workbook, because I've been able to recreate it on new workbooks also.

A recalc fixes the problem, but I distribute this timesheet to other users and don't want them to panic if they encounter the issue. Is there a limitation to the Hyperlink function that I'm running up against?

The only solution I can come up with is to make the entire workbook volatile through VBA but I don't want to do that unless I have to.

It seems to me that you are not including the full path to the filename in your HYPERLINK function - maybe you should use LEFT rather than MID.

Hope this helps.

Pete

Hi Pete,

Thanks for the reply. Left doesn't resolve the problem - I just tested it. I tested the formula below and it works until I create and follow the new link as described above:

=HYPERLINK("["&SUBSTITUTE(SUBSTITUTE(LEFT(CELL("filename"),SEARCH("]",CELL("filename"))),"[",""),"]","")&"]"&DAY(TODAY())&"!C4","To Today's Timesheet")

My mid formula in my first post works as is since it is a reference to itself, but I get these #Value errors in certain situations (the only one I've identified is in the first post).

Please try it for yourself and let me know if you get the same thing:

- create a book and put this formula in the first worksheet: =HYPERLINK("["&SUBSTITUTE(SUBSTITUTE(LEFT(CELL("filename"),SEARCH("]",CELL("filename"))),"[",""),"]","")&"]"&DAY(TODAY())&"!C4","To Today's Timesheet")

- then name another worksheet in the same workbook "22" (for today's date)

- follow the link on the first worksheet - it should work.

- now create a new workbook and put this formula in it =HYPERLINK("https://www.google.com")

- click through the Google link and then go back to your first workbook. The original Hyperlink formula should say "#VALUE"

Is your hyperlink formula jumping to a worksheet within the same file? If so, you don't need all the complications of the CELL function - something like this:

=HYPERLINK("#'"&DAY(TODAY())&"'!C4","To Today's Timesheet")

where the # symbol effectively means "in this file". I think the CELL function needs the file to be saved to work correctly, so maybe if you go to Google and then back again it treats it as not being saved.

Does that improve things for you?

Hope this helps.

Pete

EDIT: Note that I've added apostrophes around the sheet name, which you need if the sheet name is just numbers.

Awesome! It works. It must be as you said that the CELL property is hanging it up because of saving. I didn't know about the "#" for links - glad to have dumped all of that CELL related stuff.

Thanks Pete!

You're welcome, and glad to hear it worked - thanks for the rep.

Pete