Sign in to join the conversation:
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.
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.
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:
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.
Understood, Paul. I appreciate the insight and idea. Somethings *I* just have to learn the hard way!
Always happy to help and share.
how to get the formula to get the weighted average of the scrap
I want to set up Smartsheet so that if a request is submitted on the same day and the submission time is between 6:00 PM and 12:00 AM, the Request Submitted Date should automatically move to the next day. For all other cases, it should remain the same day.
I have a column titled "start date" for each of my employees. I would like a reminder sent to a manager at the 30-day mark after their hire date.
