-
Count projects for the month
I'm trying to calculate total "In Progress" projects for the month, I used the below formula to calculate, =COUNTIFS([Actual End Date]:[Actual End Date], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), [Project…
-
Rag Status for end date and % complete
Hi there, Could somebody help me with this formula for a rag status please? The one I'm using at the minute is not picking up red statuses. =IFERROR(IF([Percentage Complete]@row = 1, "Blue", IF(AND([Percentage Complete]@row < 1, [Finish Date]@row > TODAY()), "Green", IF(AND([Finish Date]@row < TODAY(1), [Finish Date]@row <…
-
INDEX-COLLECT Formula Returning #INCORRECT ARGUMENT
Hi Smartsheet Community, I've already perused a number of Community postings related to this formula, but I can't seem to work it out. On my source sheet, I have the following columns: 1) KES Number 2) Date 3) Time Spent On my target sheet, I have these same columns in addition to other data. So far, this is the formula…
-
"Locking" an Index/Match formula
Hi, I am using an index/match column formula to look up an approver from another sheet. This is working fine, but I am going to need to change an approver and want to keep the previous approver listed for the past records. Does anyone have any suggestions on how to do this? I would rather not create a new column or copy…
-
Status Column Formula
Hello Team, can you help me with status column formula to automate status column: Green = "% complete"=0% Blue = "% complete"= 100% Yellow = "Finish Date" >Today() but < Today () + 3d Red = "Finish Date" + 3d Remaining "Green" I was trying but unparseable: =IF([% Complete]@row="100%", "Blue", IF[% Complete]@row="0%",…
-
INDEX/MATCH or VLOOKUP for Date Range
Hi, 1) I have sheet "X" set up as shown in screenshot 1 below where I have got a start date and end date column and the week numbers associated for the date ranges. 2) I have got another sheet "Y" set up with empty week number column as shown in screenshot 2. Please suggest me with a formula to enter in the Week number…
-
IF(AND Invalid Operation Error
Hi! I need help fixing this formula - keep receiving an Invalid Operation error. I'm trying to return "No" if an email from the row in the Smartsheet file matches an e-mail from the cross-sheet reference's file in the E-mail column and that cross-sheet reference's row also contains "Mgmt" in the User Role column. (So two…
-
Multiple IF(AND criteria with NETWORKDAYS?
I am trying to use NETWORKDAYS with two criteria: whether there was a value in [PO Issues] + whether or not it was checked/addressed in [PO Issue Resolved]. My current formula is: =IFERROR(IF(AND([PO Issue Resolved]@row = "false", ([PO Issues]@row <> "", NETWORKDAYS([Quality Control End Date]@row, [Receiving Completed…
-
Day & Week before reminder
Hello, I would like to set a day before and a week before reminder that references a Due Date column I have in my sheet and only triggers on weekdays. Is there a way to do this? Thank you for your help!
-
Why am I get an error when I added IFERROR to formula:
=IFERROR(YEAR([Current Charleys GO]@row) - YEAR([Walmart Revised GO date from 7/8]@row)) * 12 + IFERROR(MONTH([Current Charleys GO]@row) - MONTH([Walmart Revised GO date from 7/8]@row)), "")
