-
Vlookup from another sheet to copy formula number
Hello, I am trying to determine how to get a cell copied into a sheet from another sheet based on certain criteria using a vlookup formula. Here is the sheet i would want the formula to autofill in. When Gunma (Asahi) and Aussie lemonade are chosen, I would like the formula number to autofill pulling from another sheet. So…
-
Formula to populate the last count/date of the newly added row
Hi, I am trying to see if there is a Formula to populate the last count/date of the newly added row under each Indent. For example, in at attached image when I update numbers every quarter, I manually update the new count on top and the last updated end date. Thanks, CK
-
Find/Replace on Checkbox Column
Has anyone been able to figure out how to do a find/replace on a checkbox type column? I have tried 1, true, TRUE, True, "1", "true", "TRUE", "True", and none of them seem to trigger a match.
-
IF + INT/INDEX
Hello! I'm having trouble getting this formula to work (it's giving invalid data type): =IF(AND({OTD/FTY Range 4}) = YEAR(TODAY()); IF(AND({OTD/FTY Range 1} = "1"; IF({OTD/FTY Range 2} = "90%"; INT({OTD/FTY Range 3}))))) So what I want to do is create multiple if statements. The first is if the year column is the year…
-
Date Calculation Formula excluding weekends and bank holidays
Hello - I need some guidance. I used this formula https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates?_ga=2.214560144.666552865.1680531310-742068913.1676652705 to calculate weekly, bi-weekly, monthly, bi-monthly, quarterly, bi-quarterly, annually, bi-annually dates and then I realised I…
-
How to Return ALL values from Sheet1 column to Sheet2
HI, Is there a formula to copy all values in Sheet1 column Task to Sheet 2 column Task2? Sheet1 is populated via form, so rows are consistently added. What I can't do: 1) Adding a Helper column with numbers 1,2,3,etc to Sheet1 is a no go. So I can't Vlookup from Sheet2. 2) Index(Distinct will only return unique values. I…
-
RYG status for it to be blank (no ball) in column
Hello I need some help with a formula. I have a status column that is linked to the "status of contract" column and depending on the status of the contract, the balls will be green, red or yellow. Issue I'm having is for it to be blank (no red ball) whenever the status of contract is blank. This is the formula I currently…
-
Health Field Formula Issue
I'm working on a formula to set the Health status of each task line and continue to get #UNPARSEABLE errors. The current formula: =IF(Status@row = "Complete", "Green", IF(Status@row = "In Queue", "Blue", IF(OR([% Comparison]@row <-0.249, [End Date]@row > TODAY()), "Red", IF(AND([% Comparison]@row >=-0.249, [% Comparison]…
-
Looking for Formula Help - Health Status based on End Date versus Today
Good morning! I am looking for help with a formula as what I have entered returns "Unparseable". I am looking to return colors based on the below: If Status is Complete then "Green" If Status is in Progress but less than TODAY "Red" If Status is Not Started or In Progress and the End Date is greater than today by 7 days…
-
Extract Cell History For Formulas
Hello, Is there any way to extract the Cell history to calculate formulas. What I am trying to do is create metrics off the dates in the cell history. I.E. if a Dropdown list column starts on July 1 with Value 1 and changes to Value 2 on December 10th then Value 3 on December 21st. I want to measure and tract the time…
