Hyperlink function returns "#value"

[Dexlee46]

Hello,

I'm having an error with a hyperlink function I am implemented in my order log spreadsheet. There is an IF statement that precedes the hyperlink function, which basically is to confirm that the order has not been placed, along with another constraint if an order is about $5,000 to get approval for it. The second half the "hyperlink function" contains the "email for the contact" of the supplier, the "cc", the "subject" which is the PO number, and the "body of the email", which is where I use the concatenate function to populate the body portion of the hyperlink function, which references the part number the part description, and the qty ordered, along with text. I also reference the employee's email who is requesting the email in the "cc" portion of the hyperlink.

The issue is I have a "#value" error popping up. I'm not sure this because of the syntax, because I tried deleting the part number and description, and I then can send the email, so I figure that it was an error with referencing the cells in the body of the email. I sometimes will have an error if I pull up a certain employee's email in the "cc." I'm not sure what the root cause of the issue is, but I suspect it is in the body of the hyperlink function, and the cc portion as well.

Can someone please give me a hand on this issue?

Thanks.

[#2]

Hi @Dexlee46 

I think your combined text string is exceeding the allowable 256 character limit

[#3]

@Wyn Hopkins 

Thank you for the input! So by exceeding the total limit, is this all the text encapsulated in the link location portion for the hyperlink function? If so, how do you overcome this?

[#4]

@Dexlee46 

Yep, best option is to split the string into 2 helper columns and then join those in your hyperlink function

[#5]

As a side note, be careful with the amount of conditional formatting you are applying. the file could be come super slow.
Your table finishes at row 15,155 and then you have formatting all the way down to row 1Million

[#6]

Thank you for the advice! 

Although, I'm still having trouble getting the help cells to work. If you look at the bottom of the spreadsheet for 1912038, I created two columns for the helper columns with the mail to and subject in column 1, and the cc and body for column 2. But when I copied the syntax you used to merge them, it was unsuccessful. I'm guessing the issue is still the character limit? If this is the case, the body portion is probably the issue.

Can you let me know how to proceed?

Thanks.

[#7]

HI @Dexlee46 

Apologies, I've done some more digging into this and splitting the items doesn't help with Hyperlink there is a hard 256 character limit regardless of how you arrive at it.

Doesn't appear to be a workaround without going down the path of VBA and using some external url shortner - see this post https://stackoverflow.com/questions/3893541/exceeding-max-char-limit-in-excel  no idea if it's still relevant as it's quite an old post