-
Using Index/Match/Collect with Cross-Sheet References
Hello All. Daily, four separate audits are performed. The daily audit results are collected via forms (most recent results on top), producing an ever-growing list of results in the respective daily sheets. My goal is to report data from a few columns (named identically on all 4 separate daily sheets) in reports and/or…
-
How to find difference between 2 rows, but only if data exists in both rows?
Hi, I'm trying to make a formula that says 2020 Experiential Audit minus 2019 Experiential Audit = value in Experiential Audit YOY change...but only if there is data in both 2019 and 2020. See my sheet below. I think this is an IF formula but I dont know how to say If there is a number... Thank you in advance!
-
Notification not sent to sheet owner
I have setup a notification that successfully sends an email to anyone that has been assigned to a task. However, if I assign a task to myself, I don't receive an email. Is this not receiving an email if you create the task or own the sheet normal? Mostly I was looking for the email to confirm I configuring the automation…
-
Auto-number function- broken
So I had functionality, where every time a new record was added (via a MS form) an project ID would be generated. The format is "IR- 0000" where the IR- was constant and the 0000 was a sequence #. (I would want it to start at seq # 0046) I cannot remember if I had used the auto-number column type or a formula. I am the…
-
Can i remove multi-select from single cell?
Hi folks, Bit of a novice here, i have a challenge for you 😁 We use smartsheet for tracking holiday, sick leave, unpaid leave etc. For 2019/20 we've only had a single-select option for each cell, but this makes it difficult to track when employees work 1/2 a day and also 1/2-day Holiday or 1/2 day unpaid, on the same day.…
-
Best Formula to use to COUNTIFS based on several criteria.
I'm trying to come up with a formula that will return a number based on several criteria. I have a separate sheet that I have a key of allotted days off for each area (Vacation, Personal, Summer Friday, and so on). On that separate sheet, I have an available days based on the Key, minus the formula that i'm trying to…
-
Summation of rows that have been inserted via Form
I would like to have the sum formula already in my sheet so that as voting takes place it automatically sums the total and displays in a report. I have everything set up. However when the data is inserted from the form (top or bottom) it places it above or below my formula. Is there a work around for this? Thank you
-
Countif depending on 1)string value 2)date range
Fault type Date raised Microbial 01/1/2020 Microbial 12/1/2020 Chemical 14/1/2020 Chemical 01/2/2020 Physical 01/3/2020 I have been successful in getting the count for the [fault type] through (e.g.'microbial') =COUNTIFS({Supplier Performance Range 1}, FIND("Microbial", @cell) > 0) But I would like to also do this by…
-
00 triggered as greater than zero
I have a formula to flag a column if someone enters a number greater than zero in another column. Problem is, someone accidentally entered a double zero (00), and it triggered the flag. Any ideas on a good way around this? current formula: =IF([Number of Patients tested Positive in last 24 hrs] >0,1,0)
-
Trying to do a COUNTIF two columns fall under a certain criteria
I am trying to create a formula in my sheet summary that will provide the count for a completed project with a certain firm. I only want the formula to provide the count if it is both status green and analyst firm gartner. Status being the one column and analyst firm being the other. I keep getting the error: #UNPARESABLE.…