-
VLOOKUP with multiple reference sheets
Good afternoon! I attempted to use this template to create a multiple cross-sheet reference VLOOKUP. It works for the first three sheets but everything after the first three makes the formula unparseable. Any chance I can get some help? I have a handful more to add after this (a total of 36 sheets) but I wanted to try and…
-
=IFERROR(INDEX(DISTINCT) returning #UNPARSEABLE
Hi Smartsheet Community I have a rather complicated set up with this one. I have a source sheet with the following columns: [Full Name], [Month, KES Number], [Classroom], and a helper column ([Month]+[KES Number]+[Classroom]). I'm using the helper column to identify unique instances. On the target sheet, I have: [Full…
-
formula help
Hello I have a check box to auto check to indicate next month however; I've run into an issue with January not checking and I believe it has to do with it being a new year. I found a formula that someone needed to look back from January to December and i tried to make it work for next month but it's not working. How can I…
-
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…