Go to entered date in Excel for Mac 2016

[renpilot]

Hello all experts,

I have tried some VBA solutions I found but they don't work (or maybe I am doing something wrong). I read that Excel for Mac is not as advanced, so maybe that is the reason.

I am trying to make Excel for Mac 2016 go to a date that I enter in A2. The date can be in rows b4:nc4 or b11:nc11 or b18:nc18 as I am displaying the days for three years in these rows. The entered dates in the three rows are dd/mm/yyyy but are displayed as dd/mmm to save space. The entered date is in format dd/mm/yy.

I would like this functionality as it is easier to navigate all these dates when an input has to be made in a cell below the specific date. Column A is frozen so the identifiers will always be visible when scrolling through to any given date.

Hope you have ideas

 

[felixstraube]

Last try to make it work.
Can you try this function?:

=HYPERLINK("[<filename>.xlsx]'<sheetname>'!"&ADDRESS(MAX(ROW($B$4:$NC$18)*($B$4:$NC$18=A2));MAX(COLUMN($B$4:$NC$18)*($B$4:$NC$18=A2)));"Go to...")

where you replace <filename> with your filename and <sheetname> with your sheet name?
For example if you filename is "MyFile" and sheetname is "Sheet1"

=HYPERLINK("[MyFile.xlsx]'Sheet1'!"&ADDRESS(MAX(ROW($B$4:$NC$18)*($B$4:$NC$18=A2));MAX(COLUMN($B$4:$NC$18)*($B$4:$NC$18=A2)));"Go to...")

This should work.

 

[felixstraube]

Another thing to try would be to see if the following function returns the filename or an error:

=CELL("filename")

 

[renpilot]

I am sorry, but I am struggling with even the syntax here. My spreadsheet is called test and the =CELL function has two parameters =CELL(info_type;[reference].
I enter in cell G1, but if I enter =CELL(test) or ("test") or ('test') or [test] in the formula all I get is errors.

 

[renpilot]

=HYPERLINK("[test.xlsx]'Sheet1'!"&ADDRESS(MAX(ROW($B$4:$NC$18)*($B$4:$NC$18=A2));MAX(COLUMN($B$4:$NC$18)*($B$4:$NC$18=A2)));"Go to...")

returns #VALUE! error

 

[felixstraube]

you should write in a cell just this

=CELL("filename")

without the reference which is optional. It should return something like

C:\Users\<username>\Desktop\[Test.xlsx]Sheet1

Of course on a MAC this would be different.

 

[felixstraube]

Can you also try this

=HYPERLINK("http://www.google.com";"google")

If this doesn't work then HYPERLINK is not working on you excel version.

 

[renpilot]

I am sorry - I feel like I'm wasting your time.
But here's a screen shot answering your last comment. HYPERLINK works if I open a new blank spreadsheet save it as Alfa and then copy your formula into A1. Could it be something with the test spreadsheet because I first saved it on Dropbox...

I am really struggling with the CELL formula. Even if I use =CELL(Format;A1) in the new blank Alfa spreadsheet I get the #NAME error.

 

[renpilot]

Your Google HYPERLINK even works in the test spreadsheet with the calendars in it (the one I started with)...

 

[renpilot]

Have a look at this very simple =HYPERLINK(A2;"Go to:") I tried

 

[renpilot]

It is the same if I try hypelink to a cell in Sheet2...