-
Marking Oldest 5 Projects
Hello! I have been working with some data and I want to eventually be able to pull a report of my "past due" projects and show the oldest 5 projects. To do this, I have created a helper column in my source sheet that using the formula: =IF(Date@row < SMALL(Date:Date, 6), 1, 0) in order to flag the 5 oldest dates in my due…
-
Summing Data From Another Sheet
I have a resource tracker that is set up to sum hours by resource using the =SUM(CHILDREN()) Function. I also need to be able to sum hours by Project Code (Column 4), and Classification (Column 8) by Month. Can someone advise on the best way to approach this? My first thought was to use a SUMIFS formula -- =SUMIFS([Jan…
-
Skip blank cells with YEAR function
I’m endeavoring a roll up sheet that will provide sum totals from a single row on a separate Planning Request sheet. If the source sheet has all cells filled out, the formula seems to work fine: =SUMIFS({Planning Request Range 2}, {Planning Request Range 1}, [Member]1, {Planning Request Range 3}, "Approved", {Planning…
-
Task auto-assignment breaking when projects are manually reassigned
I have a form that the team uses to request work. Once the form is submitted, a vlookup assigns the task based on criteria entered in the form, and that team-member is notified of the work request. Works great. However, the manager of the team sometimes has to manually re-assign work due to work-load issues, etc. When she…
-
COUNTIF EXCEPT
Hello, could you help me find a formula that counts all except "x". I have a Column that has data such as: dates, "ok" and "N/A". I try using the Countif formula but cant seem to figure how to have it count all except the "N/A" information. I´d appreciate the help. Thank you!
-
Smartsheet formula
Hi Can you please suggest me with a formula that carries out the following function for me: IF %column = 100%, "Complete" IF%column = >25% and <=99%, "In Progress" IF %column = <25% "At Risk" IF %column = blank, i.e. no value then "Postponed"
-
Auto-populate multiple cells with hyperlinks to web pages based on the value of one cell?
I have a sheet serving as a software upgrade checklist template "model", (not an actual template, because it contains cross-sheet functionality, which is lost in templates). The sheet includes rows of tasks and a column containing hyperlinks to web-based instructions for each task. Instructions are on different pages based…
-
OR function Not Working with Multiple Logical Expressions
Hello, I have the following formula: =IF(OR(Status4 <> "Complete", Status4 <> "Sent/Complete"), "not complete", "Complete") which always returns "not complete", i.e. true. However, modifying the expression so that only 1 logical expression is present, the expression works correctly, showing the true and false outcomes as…
-
Display text in a countif formula
I have a project type column and I want to show in the summary row at the top of my sheet the number of PoCs and Pilots. Instead of just displaying the number, I want the cell to read PoCs = 22 or Pilots = 15 =(COUNTIF([Project Type]3:[Project Type]69, ="PoC"))
-
IF Formula with Children
Hi everyone, I am new to Smartsheet so apologies if this has been answered elsewhere. I am trying to change RYG balls for a parent row based on children rows being marked done or not. I've used this formula which seems to work, but it gets tedious when there are several Child rows to include or if I want to add another…