-
SUMIFS and date
Hi All, I am trying to reproduce this excel formula in smartsheet =SUMIFS(Sheet2!C:C,Sheet2!A:A,"<="&A2,Sheet2!B:B,">="&A2) I am trying to get a result equivalent to Column B in sheet 1 (see attached file) based on criteria in sheet 1 on column A from the data in sheet 2. My formula in smartsheet is =SUMIFS({Sheet2 Column…
-
Capture Timestamps Before They Change
Is there a way to save a date that something occurs? We have rows that get changed constantly, and the status balls change from Green to Red to Yellow to Gray. Any way we can capture the date that a row's status ball changes to Red, even if it later changes to other colors? Thank you!
-
IF / OR Formula help (#Invalid Column Value)
Hi everyone, I am trying to figure out a formula, that will show the following result: * If the completion date is before the due date, the green circle appears; or * If the completion date is after the due date, the yellow circle appears; or * If the completion date is blank, the red circle appears. The formula I have is…
-
Status Update -- Auto Update the icon with a Rule
hope you may assist as I've seen you post about other Status formulas. I'm trying to write the "IF" statement to look at status update the Icon to Red, Yellow, Green or Blue =IF((AND([% Complete]@row <> 1, TODAY() - Finish@row > 1), "Red"), IF(AND ([% Complete]@row <= .75, TODAY() - Finish@row =< -5), "Yellow"), IF(AND [%…
-
Need your advise on Nested if
Hi All: Need your advice on the following problem. I have three columns [RUN/INVEST], [MUPOR] and [CHECK] in my sheet. I also have two separate data sheets SHEET1 and SHEET2. And I want to write an if else formula as below for [CHECK] column values if ([RUN/INVEST]1 = "Invest") then {Look for [MUPOR]1 value in column1 of…
-
countif column values based on a monthly value
Hi, I am creating a table of count of values in a column based on what month is reported in another. the table I want is attached. The formula I am using is as below. Can anyone advise what need to change in the planned formula =SUMIF{V2.0 Eirabot - Gantt Range 1}},"12", {V2.0 Eirabot - Gantt Range 1} I also want to have…
-
email lookup
hi, Is there a way that if I choose a name from a drop down box in one column it will show the appropriate email address in another column? regards, Mark
-
Checkbox Range Formula
Hello- Is there a formula that will check a box if another range of cells is also all checked? For example, I have a column that has 5 checkboxes in it. I want another cell to also check off as soon as all 5 checkboxes in the column have been checked.
-
Turn average working days to projected date using WORKDAY()
The basic ask is to calculate a projected due date based on historical data and duration that a certain task takes. So if x, y, and z parameters are checked, it will find the average amount of time (from a different sheet) that those parameters take and then calculate a suggested due date. I want to use WORKDAY(), but am…
-
Nested IF/AND Help with RYGB Status Balls
I'm trying to accomplish two things: First, for the "Status Visual" column, I'm trying to state the following formula: * If the "Task Finish" is 3 days overdue, and the "Task Status" is not "Complete" the ball should be red. * If the "Task Finish" is 1 day overdue, and the "Task Status" is not "Complete" the ball should be…
