-
Index collect formula question
Hello I need some assistance with my index collect function, In one cell i'd have =IFERROR(INDEX(COLLECT([Location and Event]:[Location and Event], [Column23]:[Column23], [Week #]@row, Email:Email, "Person 1"), 1), "") In another cell i'd have =IFERROR(INDEX(COLLECT([Location and Event]:[Location and Event],…
-
Subtraction Formula?
I have a value of 02 and I would like to have a formula that would return 01. Is the a way to do this? 02-?? = 01
-
Matrix Sheet based on date and personal resource assigned
Hello all I'm a bit stumped by this and would like assistance on how to create my formulas. I'm tasked to create a sheet, view, or dashboard to summary our master data sheet turning it into a matrix style sheet. This matrix style sheet will pull data from several other databases within smartsheets. The matrix should…
-
Index matching formula for cross referring between sheets
Hi everyone, I would like to get help with a formula related to index matching. I have two sheets, for the purposes of this discussion - Sheet A and B Sheet A (only has these two columns) Sheet B is a grid view sheet that has a Smartsheet intake form. My ask is - When the intake form is submitted, if Dermatology - Dr. Ozog…
-
Using COUNTIF in Sheet Summary
I am trying to use a COUNTIF formula in the Sheet Summary. I have a column called, "Current Project Status." I want to count the total number of times the word "Operational" appears in that column. I have entered this into the field with no luck; "=COUNTIF(Current Project Status:Current Project Status,Operational)" I have…
-
Change Status Based on Previous Row
I am trying to create a column formula that will change the status of a row to "Ready for Work" when status in the row above it changes to "Complete" I tried to follow the information that was provided this thread, but I keep getting #UNPARSEABLE: I already have an auto-number column (Job # - font is just in white based on…
-
Counting Entries Satisfying a Condition and Within a Certain Month/Year
Hello Everyone! I am trying to create a formula that counts the number of times a condition is satisfied for each month/year. My sheet has a column "Parameter" that either has the value "OFF" or "ON". I would like to create a formula that counts the number of times "OFF" occurs per month. However, the dates in my data…
-
How to count criteria in a multi-select column
I have a sheet that tracks the priority level of funding requests and I am trying to formulate a metric that 1) counts the number of requests under a particular priority level and 2) only counts the request if it is under a particular Division Group. The issue I am running into is that the priority levels are multi-select,…
-
Index/Match with Has and Specific Select
I have an master roster and each week I need to ensure a response for each. Since each responder has mutliple areas of responsibility, one submission may cover multiple items from the master roster. I used an index/match formula however when there are multiple entries in a single line, I get a #No Match. And now that the…
-
Can you filter a sheet to view entries between two times the days prior?
I am looking to filter a sheet to show only entries made between 6AM yesterday and 6AM the day before yesterday. I tried using a helper column with the below formula to input a value that I could then use in a sheet filter to show only those rows with a value of "1" but am getting an error in return. =IF(Created@row <…
