-
Calculate date diff from previous row
Hello Smart People - Simple question. I would like to keep a running tally of the time between dates in the same column as it progresses via rows, so [Date]@row - [Date]@PreviousRow. Make sense? I think in SQL we just use "Previous". Thanks, Mark
-
INDEX(COLLECT #INVALID VALUE error
Hi! I'm trying to solve a formula issue. I'm trying to pull text (Title) from a source sheet based on 2 criteria: Slot and Score. The slot needs to equal 1.1, and the score needs to match adscore@row. Here's the formula I created: I am getting a #INVALID VALUE error message. Other background information: {Title}, {Slot},…
-
Formula previously worked, doesn't now
Hello! This logic seemed to work in a different capacity, but no longer is. I have the three categories used locked to check box only. Help welcome! =IF(AND([Sent to VP for Signature]@row = 0, [Returned to Requester]@row = 0, [Fully Executed Filed]@row = 0), “Red”, IF(AND([Sent to VP for Signature]@row = 1, [Returned to…
-
COUNTIF with multiple criterias
I have a formula in my sheet to count the number of unchecked boxes in a column. I'd like to set a condition so that it only counts if the box is unchecked and there is a name in the "facilitator" column (Contact email format). Alternatively, if it's easier, it could count IF there is a date in the automatically generated…
-
How do I get a sum for each location (Room 600-605) excluding expired materials?
In this first image the "Complete Count QTY" column sums the materials excluding expired materials. This works perfectly. In this image you can see my attempt at getting a sum for Room 605 in the "Location Specific QTY" column excluding expired materials. This is where I have a problem. Ideally this would read 4 in the…
-
Formula to Flag or Not Flag
Goal: Flag when the due date is in the next 7 days No flag when the due date is BLANK or more than 7 days in the future Two formulas that seem to work separately: =IF(Due Date2 <= (TODAY() + 7), 1, 0) =IF(ISBLANK(Due Date2), 0) Anyone know how I can combine these formulas to make both work?
-
Issue with formula giving inconsistent answers
I am trying to identify FAILS and PASSES the question "Within how many days is your earliest routine appt" (dropdown with selection of days to chose from). Many of the states have different requirements to determine pass and fail. Therefore, the formula is somewhat complex depending on state and the requirement. Above is…
-
INVALID OPERATION error while building a IF/AND formula
I am trying to build a formula that identifies project stages based on where we are date-wise in the project. I started building an embedded IF/AND statement that was looking at each project stage date range and if TODAY's date was in that range it would identify that as the stage. I got an INVALID OPERATION error so…
-
using index match to automatically pull newly created cell data from form
I have been successful at pulling previously populated cell data from sheet A which is auto-populated by Form A to populate specific cells in sheet B; however, I have not been able to find a way to automatically pull newly populated data coming in from Form A or a recurring/cyclical basis (e.g. anytime a new form entry is…
-
CountIf and Multi Select columns
I am trying to count the number of instances a value appears in a multi select drop down column, but I keep getting an unparseable error. After searching the existing help forum I have tried both the CONTAINS and the HAS functions as follows, but receive this error for both of them. The formula's I have tried are:…