-
SUMIFS and HAS between a date range
I'm attempting a formula for finding the total number of hours when a cell contains a specific name in a given month. I've been able to make the formula work without the date range with the following: =SUMIF({Custom Request Tracker 2024 Facilitators}, HAS(@cell, [Requested Hours by Facilitator]@row), {Custom Request…
-
How can I create a formula to flag booking conflicts?
Hi there! I'm looking to create a formula that will help me flag conflicts, but I'm unsure where to start. I have a master project sheet that lists the project name, start date, end date, and company resources assigned to that project. I'm looking to create a formula that would spit back an error (or custom message)…
-
Formula to count number of child rows that with dates that fall in last 60 days
Hello, I am working on a sheet and would like a "helper" column to provide me with a number in the parent row that counts to the number of child rows with a date (from the "Date Shared" column) that falls within the last 60 days. The goal of this is so that I can pull a report that lists any rows that list dates in the…
-
WEEKNUMBER function doesn't seem to be updating promptly?
Hi, I am using the following formula to populate a checkbox called "this week?" if a fitting is scheduled to take place this week: =IF(AND(YEAR(TODAY()) = YEAR([EXPECT FITTING DATE]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([EXPECT FITTING DATE]@row)), 1, 0) I'm using the checkbox to filter a report so the user can see what…
-
Index Match Issue
I have an index/match formula pulling data from a column based on item ID, which is a unique identifier. The index/match today pulled values for every value after the desired ID. Here's the formula text: =IFERROR(INDEX({North America CAR Tracker Update Request Range 4}, MATCH([ID Number]@row, {North America CAR Tracker…
-
Clean up reference sheet labels
I've searched how to clean up reference sheet labels within a task sheet when creating formulas, and I can't seem to find anything. It gets unruly when I'm creating formulas in multiple columns. Thanks for the help! Linda
-
The expected progress percentage does not match the completed percentage.
Hello, I'm trying to set up my spreadsheet in a way that I can have a column that calculates the expected percentage of an activity for the current day based on the start date and end date. The formula I am using for this column is: Expected percentage: =IF(TODAY() > [End Date]@row; 1; IF(TODAY() >= [Start Date]@row;…
-
Help with Sheet Summary Formula
What I am trying to do: I am trying to write a formula that counts the projects that are in any of the following Stage of Project: Kick-off & Requirements Gathering, Test Setup, UAT, or Deployment AND the Type of Project is Maintenance - Break Fix. Formula I am using: =COUNTIFS([Stage of Project], "Kick-off & Requirements…
-
Formula to copy one cell unless it is empty then copy another cell
I've used the formula =IF(IFBLANK, {Source Cell}, {Fallback Cell}) but I'm getting #unparseable. Any suggestions please? (The cells it's copying are index-matched to another sheet but they both populate with "N/A" until the original source sheet is populated but it's still not copying over the "N/A". Formula: Source cells:…
-
Date Cell link
Hi All, Ive simplified my date data link needs. Hopefuly someone can help! I have template and fit dates i need to plan which is selected in a sheet called 'Active Orders' Then i need the Cell or even better link to row to be in the date in the planner. Called 'Job Schedule' So ive made another sheet and linked the info…