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.
In my job as a scheduler a common question is "which of the predecessors for this activity is driving the date?" Currently I have to rely on my own knowledge of my schedules or, worst case, I have to look at every predecessor until I find the one I am looking for. Microsoft Project is nice because it has the "inspect"…
Hi all, Is it possible to display some sort of graphic like this within a dashboard? I'd be taking data from a form and gathering total numbers of of "yes" and "non" questions to get the numerator and denominator for each row or column in the picture. I think I could do the metric sheet part of this task no problem, but…
Hi! I am trying to create DVs from this sheet that will do the following: In this screenshot example - Elizabeth is the roadmap owner, but Deb is the Calendar invite owner. So in Elizabeth's DV, I still need her to be able to see this row, but not be able to edit the Calendar Update Complete column. In Deb's DV, I need her…
