Value function

Preston

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)))))

Mike Wilday

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

Preston

Here ya go. Thanks for the quick reply!

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?

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

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

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))))))

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

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

Paul Newcome

Always happy to help and share.