3 Replies Latest reply on Aug 2, 2012 8:59 PM by GlennS_3

    #VALUE! error

    952013
      Hi all,

      I have some trouble with the #VALUE! error messages on my spreadsheet. I am working on a spreadsheet using smart view's HsGetValue formula. Often in the formulas, I would end up referencing a blank account which results in a #VALUE! messages in my spreadsheet.

      I usually solve this issue by combining the HsGetValue formula with IF ISERROR formulas. So, IF the HsGetValue formula gives me an error, it will display zero, but if not, it will display the result of the formula. However, the problem with this is since it's such a large spreadsheet and the HsGetValue formula itself is a long formula, it takes a long time to refresh and it often gives me a net timed error contact admin message.

      I am wondering if there's a way to replace all of those #VALUE! messages into other forms so it would look cleaner and neater? I would like to change it to a blank value like "-" or ""

      Thanksss
        • 1. Re: #VALUE! error
          GlennS_3
          There are two possible ways around this (this if you consider changing from HSGETVALUE to grid retrievals)
          1. In the Options, change the #missing, Zero Value and #Invalid replacements to either #numericzero or to (0)
          2. In the hsgetvalue formula wrap the formula in N(HSGET....) so it would be =n(hsgetvalue,connection,dim,dime......) N() in excel says to take any non-numeric data and replace it with zero. The downside to this is it will mask other formula errors
          • 2. Re: #VALUE! error
            952013
            N function is not working
            • 3. Re: #VALUE! error
              GlennS_3
              N() does work. To prove it, in a spreadsheet add to cell A1 the text AAAAA and to cell B1 the value of 2. In cell A3 put in the formula =A1 + B1 you should get back #Value now change the formula to =N(A1) + N(B1) you should get back the value of 2. It works the same way for HSGetValue formulas, you must have the syntax wrong