looking for an excel guru?

[Truman42]

Howdy gents, Surely among you brewers there must be a few excel gurus that can help me out with a formula im trying to do.

Basically Im trying to combine this

=IF(ISNA(VLOOKUP($C15,RatesC,4,FALSE)),"",(VLOOKUP($C15,RatesC,4,FALSE)))

A VLookup formula which also returns an empty cell instead of an #N/A if the vlookup reference cell is blank.


With this


=HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE))


So I want my formula to use vlookup to check a defined range and if column 8 has a hyperlink then display that link in the formulas cell. But if there is no hyperlink just leave the cell blank. Currently it shows #N/A instead.

I tries this one below but it didnt work. The hyperlink works ok but unless I type a value into C15 I still get #N/A in my cell.

[SIZE=medium]=IF(ISNA(VLOOKUP($C15,RatesC8,FALSE)),"",(HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE))))[/SIZE]

Any gurus out there that can provie some assistance this please?

 

[philmud]

I'm reading that on 4 hours sleep, and I am no guru, but are you essentially saying the formula returns the correct result where there's a hyperlink, but where it's blank it returns an N/A (but you want it to be blank)?

Wouldn't an IFERROR command with "" as the value give you what you're after?

 

[Airgead]

Yeah. I think you want to get rid of the if, let it evaluate and return an na then wrap the whole thing in an is error or isna function to catch it and replace with a blank so

[SIZE=medium]if(iserror(HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE)),"")[/SIZE]

[SIZE=medium]or something like that[/SIZE]

[SIZE=medium]Or you could just let it return NA and get a few hours of you life back....[/SIZE]

 

[Truman42]

I'm reading that on 4 hours sleep, and I am no guru, but are you essentially saying the formula returns the correct result where there's a hyperlink, but where it's blank it returns an N/A (but you want it to be blank)?

Wouldn't an IFERROR command with "" as the value give you what you're after?

My formula is in cell E15. Basically If C15 is blank I get an #N/A error in E15. But if I type anything into C15 I no longer get the #N/A error. The hyperlinks work and are shown in E15 if they exist in my vlookup table. if not then E15 stays blank as it should.

@Airgead..i tried that formula and variations of it but it didnt work.

 

[Diesel80]

I could probably solve this for you mate but not sure if I can get to it for a while today. When you need it solved by?

 

[Airgead]

That's probbaly because I'm an idiot...

[SIZE=medium]if(iserror(HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE)),"",[/SIZE][SIZE=medium](HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE))[/SIZE][SIZE=medium])[/SIZE]

[SIZE=medium]or [/SIZE]

[SIZE=medium]if(isna(HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE)),"",[/SIZE][SIZE=medium](HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE))[/SIZE][SIZE=medium])[/SIZE]

[SIZE=medium]I'm not sure that n/a is actually an error. So you might need isna rather than iserror.[/SIZE]

[SIZE=medium]#disclaimer I don't have excel with me right now so I'm doing this from memory. [/SIZE]

 

[Truman42]

That's probbaly because I'm an idiot...

[SIZE=medium]if(iserror(HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE)),"",[/SIZE][SIZE=medium](HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE))[/SIZE][SIZE=medium])[/SIZE]

[SIZE=medium]or [/SIZE]

[SIZE=medium]if(isna(HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE)),"",[/SIZE][SIZE=medium](HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE))[/SIZE][SIZE=medium])[/SIZE]

[SIZE=medium]I'm not sure that n/a is actually an error. So you might need isna rather than iserror.[/SIZE]

[SIZE=medium]#disclaimer I don't have excel with me right now so I'm doing this from memory. [/SIZE]

Thanks airgead, I had already tried both those formulas using hyperlinks in each section with no luck and I get a message saying the formula contains an error.

@Diesel80....Im not in a hurry. I ended up solving the problem by using conditonal formatting to change any cells containing #N/A to white text. But I would still like to know a way to fix this using formulas as there must be a way.

Thanks.

 

[Airgead]

last try...

[SIZE=medium]if(isna(VLOOKUP($C15,RatesC,8,FALSE)),"",[/SIZE][SIZE=medium](HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE))[/SIZE][SIZE=medium])[/SIZE]

[SIZE=medium]I think I counted the brackets correctly...[/SIZE]

 

[Truman42]

last try...

[SIZE=medium]if(isna(VLOOKUP($C15,RatesC,8,FALSE)),"",[/SIZE][SIZE=medium](HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE))[/SIZE][SIZE=medium])[/SIZE]

[SIZE=medium]I think I counted the brackets correctly...[/SIZE]

Awesome mate that is working. So I had my hyperlink in the wrong part of the formula then?

Thanks again for the help.

Cheers

 

[Airgead]

yep.. the hyperlink function doesn't return the excel N/A error, it returns a hypertext string containing the characters "N/A".

So check the vlookup for N/A and if true then output "" else do the vlookup and return the hyperlink.

You could also do it as

[SIZE=medium]if(HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE))="N/A","",[/SIZE][SIZE=medium](HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE))[/SIZE][SIZE=medium])[/SIZE]

but that's more complicated.

 

[pk.sax]

You don't need the IF function.

Simply wrap the vlookup function in the isna or iserror function and it should do the job. Will try from work and let u know. (I might be confusing msaccess behaviour)

I'm not a fan of vlookup, rather use the more explicit index and match function but that shouldn't make any difference to the rest of your problem if vlookup itself is working fine.

 

[Truman42]

Hi excel gurus. I had to make some changes to this excel file and now my hyperlink function doesnt work properly and returns a #REF! error. All I did was make some changes to pricing on another sheet and save as with a different file name.
Column 8 on shet RatesC that the vlookup refers to wasnt changed at all other than adding some new hyperlinks to certain rows and changing an issue with the hyperlinks not working as they linked to a folder on a shared drive.


This formula below returns the #REF! error.
=IF(ISNA(VLOOKUP($C16,RatesC,8,FALSE)),"",(HYPERLINK(VLOOKUP($C16,RatesC,8,FALSE))))
Screenshot


If I change the formula to this, I still get a #ref! error but its a hyperlink (Although still doesnt link to anything)
=(HYPERLINK(VLOOKUP($C15,RatesC,8,FALSE)))


Ive tried using the show calculation steps but it doesnt really help me fix the problem. The original file works fine and shows this



Thanks for the help.

 

[Truman42]

Ive managed to fix it. Stupid me had changed the named range ratesC to remove the last column and forgot I had done it. Anyway all good...