#NA error on one System.

[tinferns]

The below is a perfectly fine hyperlink formula. On one system it works fine.. however on another system it throws an error. I believe there is some setting in EXCEL OPTIONS or WIN10 that I need to check or uncheck.

The below Hyperlink takes the cursor the last row containing data (TEXT or Numbers only)

=HYPERLINK("[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&MATCH(CHAR(255),B1:B100,1)+1,"Hyperlink")

Please help.

Thanks

Martin

 

[MARK858]

I seem to have lost Insert Comment right click option too. Is there something wrong here

Are you sure when you right click you don't fields called "New Comment" and "New Note" now?

 

[tinferns]

Are you sure when you right click you don't fields called "New Comment" and "New Note" now?

Hi Mark.. I DON'T have either options.. New Comment nor New Note.. But I am okay with ALT+SHIFT+F2 for the moment till MS office corrects this error.

Any solution or idea's for the above #NA error ?

Please advise..


Thanks,

Martin

 

[MARK858]

Seems like this has been an issue with 2016 for a while, afraid that I can't test any of the solutions in the link as I currently have 365 in front of me.

https://answers.microsoft.com/en-us...k/b5694295-c51e-4054-b50d-92e99df3441c?page=3

 

[RoryA]

Martin,

Does the same workbook work on one machine and not the other?

 

[tinferns]

Seems like this has been an issue with 2016 for a while, afraid that I can't test any of the solutions in the link as I currently have 365 in front of me.

https://answers.microsoft.com/en-us...k/b5694295-c51e-4054-b50d-92e99df3441c?page=3

hey... this Comment issue got resolved with the latest update on MS Office.

 

[tinferns]

Hi Rory.. sorry for the delayed response.

Yes.. this formula works fine on another system using MS Office 2013.. however, does not work on another system using MS Office 2019.

Could it be a restriction with the version of MS office ?

Please advise

Thanks..

Martin

 

[RoryA]

Not the same formula, does the same workbook work on one but not the other?

There is no difference in VLOOKUP (other than performance) in more recent versions of Office.

 

[tinferns]

Not the same formula, does the same workbook work on one but not the other?

There is no difference in VLOOKUP (other than performance) in more recent versions of Office.

Hi Rory,
Yes the same File/Worksheet works well with the below formula on another system using Office 2013.

However, if I open the same File/Worksheet on the system with Office 2019, it give me a #NA error.


=HYPERLINK("[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&MATCH(CHAR(255),B1:B100,1)+1,"Hyperlink")

Please advise.

Thanks,

Martin

 

[RoryA]

I assume you're trying to use the last populated row? If so, try:

=HYPERLINK("[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&LOOKUP(2,1/(B1:B100<>""),ROW(B1:B100))+1,"Hyperlink")

 

[tinferns]

Wow Rory.. u da best.. the above formula works fine. Problem solved. Thanks a million mate.. Cheers....