#VALUE! Error

[santhoshkunder]

I get #VALUE! with this formula

HYPERLINK(HYPERLINK(Config!$B$9&"/"&SUBSTITUTE(TEXT(E38,"MM/D/YYYYY"),"/","_")&"/"&ENCODEURL(Config!$B$10))&"/"&ENCODEURL(A38&".msg","Click")

Config!$B$9= https://kt001-kt.kell.com/:u:/r/sites/AAFAA3444/Connectivity/Connectivity Controls Audit Evidence/GC.0.52.1
Config!$B$10 = Recieved from ICR
A38 = ICR - IMS vs Shell People and Active Directory - Initiated or Activated Userid_AD Obsolete
E38 =10/7/2019


Thanks

 

[pecoflyer]

Post a sample sheet Thanks

 

[santhoshkunder]

Here is the sample file

 

[pecoflyer]

What is ENCODEURL supposed to do?

 

[santhoshkunder]

It should encode the text so that it will be used as an URL.. The whole logic is with that formula I should be able to create a dynamic url which users can click on..

Without encoding: This is test
with encoding : This%20is%20test( This is fit for URL creation)

 

[pecoflyer]

Is it a XL function? A UDF?

 

[santhoshkunder]

It is XL function. I resolved it. Thanks

 

[pecoflyer]

Ah yes, not available for Xl on Mac or Web