Value function
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.
Comments
-
Hmmm. Can you share a screenshot of the data or of sample data so we can help troubleshoot?
-
Here ya go. Thanks for the quick reply!
-
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?
-
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))
-
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.
-
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:
- =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))))))
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Understood, Paul. I appreciate the insight and idea. Somethings *I* just have to learn the hard way!
-
Always happy to help and share.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!