-
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?
-
Index Large - need 4 most recent dates in a series
Hello - I need help listing out the 4 most recent invoice dates for each project (2 are shown below). I have a separate summary sheet that lists each project and was able to use Index(Max) to get the most recent invoice date then pull in that amount. Now I am stuck trying to get the other 3. Complexities are that I would…
-
SUMIFS formula syntax
Hi, I believe I'm needing help with the syntax for the SUMIFS formula. See the below photo with example data: I am wanting to total the revenue generated for each department (category) and sub department (topic) only if the status is labeled "Won" - which the above screenshot would not be included in the total. The "Topic"…
-
Possible to have a sheet automatically built from another sheet using a formula?
This is probably not possible, but this group is amazing and may have other ideas. I have an ORDER sheet that list various products and services (ITEM DESCRIPTION) into 5 different MARKET(s). This could be 100's or 1,000's of line items with many duplicates. Is it possible to write a formula that would go through each…