Hyperlink OK in Office for Mac 2011 but problems in Office 2013

Hi Ian- This might work, although it would only return the FIRST blank, if there are more than one:

Please Login or Register  to view this content.

Also, you can coerce a blank VLOOKUP return instead of a 0 by appending an empty string (Note: this would turn all numbers into text):

Please Login or Register  to view this content.

Take a look at this workbook. It uses the &"|" trick to make MATCH work correctly with blanks. Again, if there are multiple blanks in Master!column A, this always finds the first one.

NOTE: Make sure the modified MATCH array is contained in an INDEX(...,)function. Otherwise you must treat this as an ARRAY FORMULA to get proper results.

Please Login or Register  to view this content.

Hi,

Your original formula works in 2010 on Windows. Does it work in 2013 if you use a space rather than blank cell
=HYPERLINK("#'Master'!D"&MATCH(A3,Master!$A:$A,0),IF(VLOOKUP(A3,Master!$A:$AK,4,0)=""," ",VLOOKUP(A3,Master!$A:$AK,4,0)))

Hi @xlnitwit - I think OP has that part working, it's there to defeat VLOOKUP's annoying tendency to return a zero instead of a blank.
The problem is the HYPERLINK doesn't work, and I'm convinced it's because MATCH fails if match_value is blank.

Please Login or Register  to view this content.

Row\Col	A	B	C
4	a		1
5			#N/A
6	d		3

My interpretation is that the part that doesn't work is when the result of the vlookup is "", which is why I was suggesting changing that to return " " rather than "" so there is actually something in the cell to add the hyperlink to. I could be wrong though.

Originally Posted by irose_57

...Blanks are working fine - on my Mac if I click on a blank cell in the Members sheet e.g. Name, it takes me to the relevant cell in the Master sheet to edit so the hyperlink is working fine. On the Windows machine it doesn't. If there is a value in the cell it will work but not if it's blank. The problem is with the hyperlink.

What does the failed cell display? If it's #N/A, then I'm convinced blanks in COLUMN A are the problem, because MATCH can't search them without some form of manipulation. Perhaps you could upload your workbook (sanitized sample) so we can figure this out.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now click Browse, find your file, then click Upload. Simple!

And NOW I understand what YOU and xlnitwit were saying - the cell correctly displays a blank, but you can't click on it and move to the other sheet. I missed it because initially I used a different formula which didn't replicate that behavior. The problem IS the zero-length string (""). Since the "clickable" thing is limited to the text displayed, and you're not displaying any, the hyperlink fails. My apologies for the confusion.

IMHO, xlnitwit's post#5 suggests the most flexible solution. (I used extra spaces and turned off underlining in the hyperlink cells).

BTW, if you employ a R1C1-style reference in your hyperlink and change the column # dynamically, a single formula (in B3) can be copied across:

Please Login or Register  to view this content.