-
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…
-
Combining IF and ISBLANK
Hello, I have these two formulas working separately on a four-color symbols column, but I cannot combine the two of them without getting error messages. =IF(ISBLANK([M1 Completed Date]@row), "Gray") =IF([Milestone Due Date 1]@row >= [M1 Completed Date]@row + 9, "Green", IF([Milestone Due Date 1]@row <= [M1 Completed…
-
Convert a text to value number
Hi, I'm trying to convert the answers that I got from a survey to a value that I can calcul after. Example : 13 people answer : 4 (agree) 5 (disagree) 2 (no aswer I would like to convert "agree" to value of 5 points, "disagree" for 1 points, "no answer" for 0 points. Please what should I do... Thank you :)
-
index/match based upon partial description
I have scoured the community and unable to find something that works. It comes close. but not quite. The closest I have gotten is using this formula =IFERROR(IF(CONTAINS("bricks", {Training}), INDEX({Completion Date}, MATCH([Email]@row, {email}, 0))), "") However it returns the date for the first item for pig1 (1/23/24)…
-
Metric Sheet Formula
Hi All, I'm trying to write a formula to achieve the following: In the source sheet: Check if the "Resource Type" column contains "Analytic/Data". If it does, it counts the occurrences where the "Deadline Health" column contains the value of the current cell in the "Description" column, otherwise, it returns an empty…
-
Sumif Match within a Date Range Help
Hi There, I'm working with this formula: =SUMIFS({Weekly Sheet - Company Name Range 1}, {Weekly Sheet - Company Name Range 2}, ="Initiative Name", {Weekly Sheet - Company Name Range 3}, >=DATE(2023, 10, 1), {Weekly Sheet - Company Name Range 3}, <=DATE(2023, 10, 31)) This formula pulls from a another sheet and says if it's…