-
#Boolean Expected? #Invalid Operation? I can't win for losing
I thought I was quite brilliant when I figured out that the reason a formula I created in a checkbox only column returned a #BOOLEAN EXPECTED error. I solved the problem by appending +"" to the end of my formula, and celebrated with a fun sized Snickers. Imagine my disappointment, when I tried to create a new formula that…
-
COUNTIFS for how many blank cells in a column, but only if another column has a populated cell
For our smartsheet we keep track of vehicles that are or are not repaired. To keep track of how many "open claims" we have I am trying to count how many blank cells we have in the column that signifies if something is complete, but when I try to just count those cells it counts the automatic additional rows on the bottom…
-
Variance Netdays
Hello everyone, I read some of the threads about this issue, but cannot find the right answer. Members of my team are consultants and work primarily on weekends so I am using the NETDAY function to calculate the variance. If I use the formula as is, meeting the deadline will give a "1" instead of 0. To avoid this I added a…
-
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…
