-
get 2nd word from cell if cell not empty, else first 9 letters of the word if there is no space
I have =IF([Source Detail]@row <> "", MID([Source Detail]@row, FIND(" ", [Source Detail]@row), 100)) what it is missing is if there is no 2nd word, then give me 9 letters from left of the word. Above formula works when I have following word in Source Detail : Source Detail : Testing This Above formula doesn't work when I…
-
How to change Column Values into Row Values?
Hi All, I hope you are doing well. I have a question that 'how to change column values into row values?'. Can you please guide me in this regard? Thanks in advance.... Thanks, Suneel
-
Count if text contains certain words in one column and another column is not blank
Hello! I am stumped on a formula. I am trying to count the number of times the task "Full DD" is listed in the task column the cell in the "Number Completed" column is not blank. I have this formula: =COUNTIFS((Task:Task), "Full DD")(AND([Number Completed]:[Number Completed], NOT(ISBLANK(@cell)))) but it keeps coming back…
-
Combining ISBLANK and TODAY formula for RYG Symbol Column
Hi all, I'd like to automate the Health column in my Project Plan but am not sure how to go about doing this. The Health column is a symbol column (red, yellow, green). Ideally, I'd like for it to show the red symbol if the end date is 3 days late, yellow if the end date is 2 days late, green if we're on track, and blank…
-
Split Text to Columns Using Formula?
If I have a column with text like this: Mac:Air Mac:Pro Lenovo:Yoga Using formulas, how can I pull the data that comes before and after the semicolons? I want to end up with 1 column like this: Mac Mac Lenovo And another column like this: Air Pro Yoga Thank you.
-
Sumifs?
Hi, From another smartsheet I am trying to pull the total % of time spent by individual project managers on various projects that are in progress. I thought it would be a sumifs formula but it's giving me an answer of "0" instead of the percentage. Below, I have it first pulling the % column, then the Project Manager…
-
Nested If formula for RYG
Good morning, I'm having an issue with the following formula. The red and green come out, but the yellow comes back blank. Any assistance would be appreciated. =IF([% Complete]@row < 0.7, "Red", IF([% Complete]@row <= 0.07 - 1, "Yellow", IF([% Complete]@row = 1, "Green")))
-
Any way to have a formula automatically change input when a set of new rows added?
Hi, trying to automate a process whereby the change from week to week on a project is reflected in a graphic on a dashboard. Right now, I have the source sheet for the data copy the rows to a separate sheet once a week. I want a formula to simply find the difference between last update and the new one. This is what will be…
-
How to separate multi select values and collect distinct values?
Each period (P1,P2,P3 etc) we collect KPIs for our projects. In some new KPIs we have been asked to collect the answers involve multi select drop downs. What we would like to be able to do is to each period have a list of only the unique values. I have tried using a formula which uses JOIN, DISTINCT and COLLECT and it…
-
VLookup crossreference "#INCORRECT ARGUMENT SET"
Hi, I have been having trouble with VLOOKUP function in Smartsheet's. I have a sheet that I want to reference the Store# to pull over data such as the address from the other sheet. I have tried a number of things but the formula I am trying to use currently is: =VLOOKUP([Store#]@row, {ALL Store Openings (Master) Range 3},…
