-
add an IF statement to an INDEX MATCH
Hi, I am using an INDEX MATCH equation to pull a Unique ID from my Master Contract Data sheet. The equation works well like this: =IFERROR(INDEX({Contract Master Data - Contract Unique ID}, MATCH([Contractor Name]@row, {Contract Master Data - Contractor Name}, 0)), "No Match Found") However, I would now like to add an IF…
-
Checkbox Formula Not Working in Metrics Sheet
I am trying to make a metrics sheet that shows how many checked boxes there are in my sheet. The tutorials I found said that "true" and "false" mean whether the box is checked or not. Why are my results showing as 0? I should have at least 20 as checked. I used the formula COUNTIF({checkbox 1}, [Column7]@row) This was…
-
Equation to sum children, if no children then return value from a different column
Hi - I"m looking for an equation in column A to sum the value of column B for any children rows. However, if there are no children, then I would like the equation to return the value in column B. I have a helper column set up so that parent/child can be distinguished with 0, 1 or 2 value based on hierarchy. Any guidance…
-
Creating a Perpetual Quarterly and Yearly Count
Hello All! Any thoughts or help would be appreciated. I am trying to use my Month Renewal Date to create my Quarterly Renewal Date. However, my formula is not providing an out put. =IF([Month Renewal Due]@row = "November", "Q4", IF([Month Renewal Due]@row = "January", "Q1", IF([Month Renewal Due]@row = "February", "Q1",…
-
Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion. My current formula (which works perfectly) is: =JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ") However, I want to…
-
VLOOKUP for Multiple Sheets
Hi - I am trying to use VLOOKUP across multiple sheets. I have one column that is labeled clinic #, and I want to search for this clinic number across 2 different sheets, and we want to display a specific cell from that line it pulls. I have no problem doing a VLOOKUP for one of the sheets, I am running into an issue when…
-
Formula Help to Set up Sprints
I am using this formula to set the sprints in my project plan but get the error "your formula syntax isn't quite right". Looking for help in what I am missing. Thanks = IF(AND( [End Date]@row > DATE(2024, 11, 27), [End Date]@row < DATE(2024, 12, 10)), "Sprint1", IF(AND([End Date]@row >DATE(2024, 12, 11), [End…
-
Parent Color Symbol, Based on "Average" of Child Symbols
Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive…
-
INDEX/COLLECT with both MATCH and CONTAINS criteria
I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula. Source Sheet: Destination Sheet: In the Attending School column in Destination Sheet, I'd like to…
-
Formula for 24hour clock overlap - booking clashes at the same time on the same day
Hello all, I am hoping someone out there has a smart idea of how to pick up on reservations overlapping within a 24hour time period. In this instance, there are two people parking in same bay on the same day. Formula I have is: =COUNTIFS([Date Of Parking]:[Date Of Parking], [Date Of Parking]@row, [Bay Number]:[Bay Number],…
