-
Stuck on If Statements
Hi team, I'm building a dashboard and I need to show the current metric. I thought I'd show this by showing each month (for a chart) and using this formula to obtain the current: =IF(DEC3 = "", NOV3, IF(NOV3 = "", OCT3, IF(OCT3 = "", SEP3, IF(SEP3 = "", AUG3, IF(AUG3 = "", JUL3, IF(JUL3 = "", JUN3, IF(JUN3 = "", MAY3,…
-
How do I return a value, based on a date, between dates? (versus week numbers)?
I am trying to return a value to a cell, based on a date condition at the top of the column. For example, if there were 52 columns of the "week dates" in a year. The parameter for the return value is IF the date condition falls between a start date and finish date. I have been able to do this when I convert all dates to a…
-
Formula for selecting correct Image
Hey folks looking for IF statement that would allow me to specify a number ranges from one cell that contains a number to then display the appropriate Arrow. Any help in changing the formula would be great. Below is not correct but hopefully gives you the idea =IF([Heat#]1 <= 4, "Down", IF([Heat#]1 <= 8, “Angle Down”,…
-
Copying comment text into another cell but only when a certain value is triggered
Hoping you can help! I'm trying to setup a formula where if I have a Red RYG ball in a cell of a task row then the comment next to it copies into a different cell (on the same sheet) for reporting. I know I could just create a report which looks at values in those columns and trigger only when there's a red ball along with…
-
Is there a limit to the length of If/And Statements?
I have the following statement and it works for the bold section but when I add the italic section I receive an 'UNPARSABLE' error. Thoughts? =IF(AND([From Calculator]2 = "<100", [From Calculator]4 = "<25"), [Column2]2, IF(AND([From Calculator]2 = "<100", [From Calculator]4 = "26-50"), [Column3]2, IF(AND([From Calculator]2…
-
Counting for this year only (Formula help)
Hi All, I have a sheet that has been populated via forms for the past 12 months. What i would like to do is use a count if formula but only when the date is this year. my current formula is =COUNTIFS({Pure Gym Issue Type YTD}, "HR / Lateness or leaving early") This works really well but counts everything on the sheet, can…
-
Countifs with Multiple Criteria in the Same Range
I am trying to create a formula to count the number of projects assigned to one person based on the phase of a project. My criteria is that is NOT one of the following phases: Construction, Completed, Punch, On Hold. I know I need to use <> to create the "not equal to" but for some reason, I am missing something to make it…
-
automate RYG balls
I'm a smartsheet newbie and i'm ultimately trying to build a formula that automates task progress based on the following: If % Complete = 100, return Blue If it’s not scheduled to start yet, return Clear If it’s past the scheduled finish date, return Red If % complete >= (today date-start date)/task duration, return Green…
-
Formula Help
Hi All, I am in need of some formula help! 1. I would like to count all of the projects in my portfolio that are in the Active state and either Yellow or Red health. I have a Dropdown List column for State and a Symbols column for Health (RYGB balls). This is the formula I've have been trying. I am getting the Unparseable…
-
Cross sheet formula - Reporting by week
Hi Again! Is it possible to create data that provides QTY of selections checked per week and record this on a sheet automatically with formula. So we are managing live installation projects, with a high turnover of projects. Our main sheet is called PROJECT MILESTONES where we track all the live project activity. Within…