#NA error on one System.

tinferns

Board Regular
Joined
Aug 18, 2009
Messages
150
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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


 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Looks to me like you will get that error if there is no text values in B1:B100.
 
Upvote 0
Hi Steve.. thanks for your reply. There is text between B1:B100.

I have tried the same formula on another system, with same content.. it works.. And on one system it gives #NA error.

Please help.

Thanks

Martin
 
Upvote 0
Thanks Steve for looking into this..

Created a File Named: Test File
Inserted this formula in Sheet1 - L3 : =HYPERLINK("[Test File.xlsx]Sheet1!$B$"&MATCH(CHAR(255),B1:B10,1)+1,"Hyperlink")
Typed tinferns in B1. And left cells from B2 - B10 blank.

L3 shows #NA

I wish i could take a screenshot and send.

Please help.

Thanks,

Martin
 
Upvote 0
Its only this that can cause n/a:

=MATCH(CHAR(255),B1:B10,1)

What does it produce? What about this?

=LOOKUP(2,1/(B1:B10<>""),ROW(B1:B10))
 
Upvote 0
Which version of Windows and Office, and what regional settings are on the computer that doesn't work?
 
Upvote 0
Hi Steve.. This Hyperlink takes my cursor to the last updated cell +1 in that column. Its a huge file. Jumping to the top is easy... however jumping down is more helpful with this Hyperlink formula. Got this formula from LINK: https://www.get-digital-help.com/qu...in-a-data-set-using-excel-hyperlink-function/

I prefer a Hyperlink to get the job done. Can you think of another option ?

Please advise.

Thanks

Martin.
 
Upvote 0
Hi Rory.. I am using WIN10 for Home with MS office Home and Student 2019 students edition. I believe I have latest version of WIN10. Not sure about MS office. Lately I have updated both WIN10 and MS office and now Excel looks different and I seem to have lost Insert Comment right click option too. Is there something wrong here ?

Please advise

Thanks

Martin
 
Upvote 0
Hi Rory.. its a LICENCED MS Office home and student 2019 edition.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top