-
Rolling Sumif based on weeknumber.
Hello, I am trying to program a metic that sums the total based on the previous weeknumber. This should continue automatically populate each week. I'm thinking it looks something like: =SUMIFS({plug}, {cut week #}, <=WEEKNUMBER(), {cut week #}, >=WEEKNUMBER(-1)) Thanks so much for your help
-
Create a formula that returns the current project phase in the project summary metadata sheet.
The phases in my project plan are Level 1. The formula should return the text value of the phase (task name) that has the least % started, NOT including phases at 0% or at 99% and higher. =INDEX({Task Name}, MATCH(MAX(COLLECT({Level}, {Level}, AND(@cell, 1), {Level},1) AND(MATCH(MAX(COLLECT({Project 10 - % Complete},…
-
Unique dropdown values in cells from linked sheets?
I want to create a unique dropdown menu based on a link from another sheet. On the linked sheet, one column will have a list of IDs and the second column will have a names that are associated with one type of ID. Each name has its own unique row, and IDs may appear multiple times for a group of names. The linkee(?) sheet…
-
Cross Referenced sheets in a new environment
Disregard. I got it figured out. I shared and 'saved as new' a source sheet and a sheet containing 2000 cross-referenced cells to a different smartsheet account. The 2000 crossed-referenced cells now say #Permission Error. Is there any way to reactivate the formulas so they connect to the source sheet in the new…
-
Help with creating formula
Hello - I need some help with a formula.... Hopefully someone from this community can provide some guidance. I have the following sheet with multiple columns. I am trying to sum up the total "Approved Change Order Amount" by "Category". For instance, rows #1 and # 6 have the same category (Regulatory Submissions). Based on…
-
AVERAGEIFS
I've looked through the community and think AVG(COLLECT) is the answer to needing two variables to match before averaging. I'm also referencing another sheet, which isn't an obstacle for me any more. I have a formula that works but need to add a 2nd variable. What I want to do is create a "calculator" that an office leader…
-
I need a formula to flag a task
I need help with a formula. I want to use this formula for the Due Date but I need to modify it so I can add in criteria if the Start Date has been passed & the task Status shows either Delayed or Not Started =IF([Due Date]@row = "", "", IF(AND(OR(Status@row = "Not Started", Status@row = "Assigned", Status@row =…
-
Can a checkbox be disabled if other checkboxes are clicked?
I have checkboxes in use for scoring to identify priority of a project. Any combination of the first four checks is fine, but checking a box in the fifth column would indicate that nothing should be checked in the first 4 columns. Currently I just have a helper column that turns red and bold if there are certain entries in…
-
Multiple IF Statements
How would I string these IF statements into one cell? IF(OR([Project_Group]@row = "ASOS", [Project_Group]@row = "NEXRAD", [Project_Group]@row = "Grandview", [Project_Group]@row = "NWR", [Project_Group]@row = "VOIP Sites", [Project_Group]@row = "OneNWSNet/NCEP"), "NWS") IF(OR([Project_Group]@row = "AAMB",…
-
IF formula for math equation?
Hello it's me again with another question... I want to create a simple formula where VALUE cell * % Complete but only when status is "in progress" So in my example, "value of % complete" colum, line 1 would say $500, and line 2 would be blank. I know there must be a way, but I can't figure it out. Blame this one on being…