Dec 12 2019 02:11 PM
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.
Dec 12 2019 03:27 PM
Dec 13 2019 06:10 AM
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?
Dec 13 2019 04:31 PM
Yep, best option is to split the string into 2 helper columns and then join those in your hyperlink function
Dec 13 2019 04:35 PM
Dec 16 2019 08:04 AM
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.
Dec 16 2019 03:57 PM
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