Value function

Preston
Preston ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Having trouble with the formula below. Version 1 works and extracts what I need from a cell. I need to then convert that to a numeric value. Currently I get #invalid value. I'm missing something, and I bet it's dumb. ;)

 

  1. =MID([Active Link]@row, (FIND("/", [Active Link]@row) + 1), ((FIND("*", [Active Link]@row) - 1) - (FIND("/", [Active Link]@row))))
  2. =VALUE(MID([Active Link]@row, (FIND("/", [Active Link]@row) + 1), ((FIND("*", [Active Link]@row) - 1) - (FIND("/", [Active Link]@row)))))
Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hmmm. Can you share a screenshot of the data or of sample data so we can help troubleshoot? 

  • Preston
    Preston ✭✭✭✭✭
    edited 12/26/18

    Here ya go. Thanks for the quick reply! :)

    2018-12-26 15_26_45-Clipboard.jpg

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    This is clearly a bug. I pulled apart your formula and you are right, it works fine. Please contact support and reference the url to this community article. I would love to hear the outcome of the ticket.

    What's interesting is, if I put the outcome of the first formula you provided in a column called "Helper", and then I create a new column and add peform the value on the output of your formula, it works. =value(Helper@row) 

    Helper is just the name of the column I chose, it could be anything... 

    Do you have a formula creating that active link?

    2018-12-26_13-25-30.gif

  • Preston
    Preston ✭✭✭✭✭

    Mike, I appreciate the response. I will file a support ticket and post the results. You are correct. There is a formula (see below) in the source cell I am linked to in the Active Link column. However, it does not appear to matter if the referenced cell (via formula) has a cell link or not. Again, I appreciate the input.

     

    • =IF(COUNTIF(CHILDREN([Assigned To]@row), "Change") = 0, "", COUNTIF(CHILDREN([Assigned To]@row), "Change") + " / " + SUMIF(CHILDREN([Assigned To]@row), "Change", CHILDREN([Contract Amount]@row)) + " * " + SUMIFS(CHILDREN([Contract Amount]@row), CHILDREN([Assigned To]@row), "Change", CHILDREN([Submits Approved]@row), 1))
  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    I don't know if you can use a helper column or not in the meantime, but you could create a new column, called "helper" then add your mid formula there... then hide the column, and then do a value on the helper@row or specify the row number. Please let us know what Support says. 

  • Preston
    Preston ✭✭✭✭✭

    Mike, I barely started down the helper column path when tech support came through. Working of off Support's response, I was able to get this going in my direction by changing some things (see item #1 below for my revisions). Thanks for your help and response! :)

    Alicia from Support (Thanks Alicia!):

    In reviewing the formula 1, it appears it references some empty spaces in addition to the numbers. Please try to adjust it by changing the numbers being added/subtracted in the FIND function. In my testing it was resolved by using:

    =VALUE(MID([Active Link]@row, (FIND("/", [Active Link]@row) + 2), ((FIND("*", [Active Link]@row) - 3) - (FIND("/", [Active Link]@row)))))



    You my need to adjust the "2" or "3" depending upon how many spaces are in your cell.

    My revisions:

    1. =IF(FIND("/", [Active Link]@row) > 0, VALUE(MID([Active Link]@row, (FIND("*", [Active Link]@row) + 1), ((FIND("_", [Active Link]@row) - 1) - (FIND("*", [Active Link]@row))))))

    2018-12-26 15_26_45-Clipboard.jpg

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Glad they were able to help. I picked up on the spaces before seeing that support had gotten you squared away. I learned the hard way that "1" will work, but " 1" (with the space) won't. If there is the possibility of a space being on either side of the number you are trying to pull, you could also work in a SUBSTITUTE function (just as another option if formatting happens to change). Something along the lines of...

     

    =VALUE(SUBSTITUTE(.........Your normal formula..........., " ", ""))

     

    This essentially removes all of the spaces from your result before running the VALUE function.

  • Preston
    Preston ✭✭✭✭✭

    Understood, Paul. I appreciate the insight and idea. Somethings *I* just have to learn the hard way! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!