-
Formula not returning the correct values
I'm having a bit of a weird issue. I have a helper column that is supposed to return values that are pulled for a metrics sheet. Overall the formula is working, but it's like its not moving past the "Materials Delivered to Vendor" part. =IF([GM Status]@row = "PO Created", "Written", IF([GM Status]@row = "Delivered to…
-
I need help rolling up multiple weeks of data into one response based on specified criteria
I currently have a sheet that collects weekly attendance info from multiple stakeholders (for the purpose of this question we can just look at the Coach Info columns). There are 3 responses that can be put into this column: 'Yes', 'Some, and 'None'. My second sheet is a monthly tracker in which I am trying to read four…
-
#NO MATCH result with INDEX/MATCH formula referencing Auto-Number/System
I use the INDEX/MATCH formula across many of my sheets for many years. This is the first year I've encountered this issue: my formula is indexing information referencing an auto-number system column type. We generate 3-digit "reference IDs" in this auto-number column to make it easy to index information for that respective…
-
VLookup from multi select "Search Value"
=VLOOKUP(Preceptor@row, {VLookUp for Preceptors MCE Name}, 5, false) Trying to pull one answer from a search value that could have multiple options. Preceptor@row is a multi select drop down, and I only need to pull one value from any of the possible options in the preceptor@row (because it will be the same no matter who…
-
Count(Distinct(Collect Help
Hi All, I'm trying to count the number of 'distinct' participant to events based on the year and add that count to their org leaders. In the table where I have the participant I've alredy added helper columns to identify their leaders at different lvl. My formula works fine as long as I point to the specific column where I…
-
IF Formula for Date Ranges
Hi, I am looking to create a formula that would automatically assign a quarter (Q1, Q2, Q3 or Q4) based on the start date entered in another column. I would need to have the formula evaluate the date to see if it falls within a certain range (example, Feb 1 - Apr 30 would populate Q1 etc). How would I write out the date…
-
Formula for Index/Match Multiple Criteria
Can someone help me with an Index/Match or Index/Collect formula to accomplish the following logic? When {ArchiveCSYYXXXX} = CSYYXXXX@row AND {Task} = "Final Signed", provide {FinalSignedDate} In case it's relevant {ArchiveCSYYXXXX} and {Task} are child rows on their sheet. Thanks so much in advance!
-
Counting Unique Values, but only if they are within a certain date range
As the title says, trying to count rows, but only unique values and within a certain date range. Formula I'm trying to use: =COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, AND(MONTH(@cell ) = 8))) Example Data: Sales Order Number = {SO Number} and Actual Ship Date = {Date} Any help please
-
Getting Data From Another Sheet to Populate in Another Sheet when Conditions are Met
Hello, I currently have 2 sheets that I am working with: the first is the "Original" sheet and the other is a "Metrics" sheet, which pulls data from the original (being used for Dashboard charts). The "Original" sheet has an "ID" column, a "Status" column (either "OPEN" or "CLOSED"), and "# of Days" column. On the metrics…
-
If formula with Date and Time
Hi. I created the formula below to close a dynamic view on Oct.30th. =IF(TODAY() <= DATE(2024, 10, 30), "Open View", "Lock View") How do I adjust that formula so that the view can be locked at 5pm on that day?