-
Match Statement with Multiple Sheets
Hi, Due to the limited restraints on the # of rows (5,000) in Smartsheet - I need to create a formula that references multiple sheets for a match statement. See below: =IFERROR(IF(MATCH(([Employee ID]1 + [Current Position]1 + [Training Name]1), {Completions(1) Range 1}, 0) > 0, "YES", "NO"), "NO") I have another sheet…
-
Is it normal to count empty rows when selecting the entire column?
I have a formula that looks like this: =SUMIF([Request Status]:[Request Status], "5-Complete", [Est Days to Scan]:[Est Days to Scan]) I've noticed that in each of the two columns referenced in my formula, the selection includes 10 rows of blank cells below the last populated cell. Is this normal?
-
Bug or Feature?: Used in Formula does not display for Contact List
Title says most of it, so I'll repeat it: Bug or Feature?: Used in Formula does not display for Contact List Hover over the [ContactList] column which is referenced by formula on the destination sheet does not ever display the "Used in Formula ... " pop-up. I did not see anything in the Help articles to indicate this is a…
-
help with If function
I have the following if function (IF([Audit Start Date]3, ([Audit End Date]3 + 7), "Audit to be Scheduled") and I get Invalid data type. the formula works when there is no date in column. However, formula does not work when there is a date in column. I dont know what else to try. This formula works in excel...
-
RAG Rating Formulas
Hi, I am struggling with RAG rating formulas (and its something that I have done before but I can't get my head around it at the moment!) I am trying to do the following tasks: Regardless of the end date, if % complete is 100%, then show a green rag rating If the finish date is within 30 days and the % complete isn't 100%,…
-
Formula for summing the value in one column when it matches another column
I'm looking to put a formula together that will look at the values of one column and then grab the count in a corresponding and return the total value as they match. Example: I have a formula sheet that pulls in project phases and the number of risks by priority for each project. What I'm looking to do is total up the…
-
At Risk Flag
Hi there! I am trying to have it so that the flag shows if the End Date is 3 days away AND the Status is "Not Started" (ideally, I would like it to show if the status is anything but Completed, but that may be too difficult). I know to use a nested IF/AND function, but I keep getting errors when I write it out. I started…
-
Help with countifs formula
I am a bit new to complex formulas so hoping someone can help. I am trying to add a column on one sheet, that falls into two basic categories on the same sheet. The formula I am using is as follows =COUNTIFS({MM Non Prod Issue Tracking System}, [Distinct System]@row, {MM Non Prod Issue Tracking Days Old INT}, <=30, =[down…
-
Sumifs question
Failry new to Smartsheet, I can do this so easily in Excel, but not in Smartsheet. I'm basically trying to add an amount requested based on whether or not the request was approved. The approval is a dropdown menu with only 3 text status - Submitted, Declined, Approved. I want to add the Request Amount column and only the…
-
Project Approval Received / Master Project Sheet Populated
I'm trying to setup an intake process where a new project is submitted through a form and appears within a project intake sheet for review/approval/denial. If approved, I'd like certain, if not all, column values for new projects to be copied over to a master sheet, which includes additional columns for project charter and…