-
Adding months automatically
Hi all I am trying to automatically add a 6 month review date to a start date, it is working fine until a start date is past June, to which I get a #invalid value error. The formula I am using is: =DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)) So 1/6/19 becomes 1/12/19 but if I try with 1/7/19 I get #invalid value instead of 1/1/20.…
-
Why does my nested formula work for some cells in the column but not all?
#INVALID OPERATION is the error message I receive for some but not all cells. =IF(AND([Created By Date]@row < TODAY(-365), [Target Completion Date]@row < TODAY()), "Red", IF(AND([Target Completion Date]@row >= TODAY(), ISBLANK([Project Status]@row)), "Yellow", IF([Project Status]@row = "Complete", "Green", IF([Target…
-
Same Column - Row Type Header = Sum value of Row Type Detail
Based on the image below There are two Row Type: (1) Header and (2) Detail. JE REPORT is the grouping of these records. AMT = free form data entry Debit = if(AMT>0, AMT, 0) Credit = if(AMT<0, AMT,0) Question: I want to sum Row Type = Header for all Detail rows of the same JE REPORT. The first row would sum of RowNum 00074…
-
How to enter specific text if the cell contains a partial match?
My If statement: =IF(OR([Task Name]@row = "Discovery", [Task Name]@row = "Analysis", [Task Name]@row = "Build", [Task Name]@row = "Test", [Task Name]@row = "Deploy", [Task Name]@row = "Design"), "PMO", IF(OR([Task Name]@row = "PER", [Task Name]@row = "PRT", [Task Name]@row = "CK1 RTS", [Task Name]@row = "CK2 RTB", [Task…
-
Unparsable on Avg Collect
Good evening, all! You know those times when you've looked at something for so long and it eventually starts to blur together? Well, that's where I'm at, and could really use your help with this formula. In the A16 sheet summary field, I'm trying to bring in the average of the age column where the following conditions are…
-
Create a formula based on time
Good day...I am wondering if there is a formula that can bring back results based on time for example today()-created row brings back days...can I bring back hours or minutes?
-
COUNTIF CHILDREN & GRANDCHILDREN
I set up several Smartsheets for different programmes at work which need summaries pulled through to dashboards most of which i use formulas along the lines of =COUNTIF(CHILDREN([Overall RAG]@row), "Red") which has worked fine for the first few sheets. One of the programmes has introduced grandparents rows but i can't get…
-
If and when..
The thought process is a have a column with Multiple drop downs. If Service is selcted I would like it to take the date column and add a six month date for followup. The formula I was thinking is: =IF([What work type was completed?]@row, ="service", Date@row + 180, blank) However, I must be missing something. UNPARSEABLE…
-
Impossible to visualize groupings more than 2500 rows...
Hi, Im searching over the internet a variety of things (data) from a variety of types (countries, format, source, etc) and some of them are validated (true) and some dont (false) and inserting this information in a Smart Sheet page. On the other hand, I have this REPORT, reading from that sheet i have just mentioned, and i…
-
Why can't I convert this formula into a column formula?
Hello, I have sunk quite a few hours into trying to figure out why I can't turn the formula below into a column formula. Does anyone wiser than I have any ideas? =IF([Start Date]2 > TODAY(), "Blue", IF(AND([% Complete]2 < 1, [End Date]2 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green"))) The formula…
