-
Summary reporting with conditional formatting
Hello! I'm trying to build out a way to dynamically report on 58 state/territory sheets. Ideally, this report would have the total number of rows in the state sheet, plus the number of rows that meet two separate sets of conditions, with conditional formatting applied. I've tried doing this two ways: By using a sheet with…
-
Exclude and If statement then "" and based on date then "Return Value"
=IF([Tier Status]@row <> "Group D", IF([Escalation Required]@row, <>"", (IF([WM Next Meeting Date]@row = "", "No Meeting Scheduled", IF([WM Next Meeting Date]@row = TODAY(-14), "Pending", IF([WM Next Meeting Date]@row > TODAY(-1), "Scheduled", IF([WM Next Meeting Date]@row < TODAY(-14), "Overdue"))))),"Escalated")) What…
-
I want my Status column to give me a red circle when it detects a flag in my Critical column.
=IF([% Complete]@row = 1, "Blue", IF([% Complete]@row > 0, "Green", "Yellow")) I want to add there to turn red when it detects a flag in my Critical column. Please, help
-
Formula to capture last value within a column
Hi Community. I need a formula that will capture the value in a cell, from the last populated row in a sheet. Of course, the last row could be row 20, 200, 2000, thus the reason I can't point straight to it. Thoughts?
-
Countif numbers of dates in a column between two dates
Hello, I'm trying to automatically calculate the number of dates (in another sheet) which are between two dates in my master data sheet. I can use the COUNTIF function to find the number of dates on/after my first date using the following function: =COUNTIF({Enquiry Tracker - Date Raised Column}, >=[Reporting To (Week…
-
Formula syntax error
The current formula I have that works for calculating Labor hours is below =IF([Actual Duration in Hours]@row = 0, ([# of Staff Needed]@row * ([Baseline Duration in Hours]@row * 18)), ([# of Staff Needed]@row * [Actual Duration in Hours]@row)) The issue with this formula is the labor hours are always calculated even if the…
-
Is there a way to make a field required or highlighted based on a value from another column?
I would like to make our columns Risk/Issue and Mitigation either required or highlighted if our columns State or Dates Alert are in either Yellow or Red. Additionally, I would like to clear this field if both the State or Dates Alert are green if there were previously notes in those fields. Thank you!
-
VLOOKUP
Column1 Column2 Column3 1 1/1/20 Complete 3 1/1/20 Pending 2 1/1/20 Pending Column 4 Column5 Column6 3 1/1/20 Complete 2 1/1/20 Pending 1 1/1/20 Complete I need a formula that can identify when one of the cells in the first three columns is different from the cells in the last three columns. In this example, 3 should be…
-
Need to return a value with INDEX(COLLECT) but not if cell is BLANK
Hello, I am unsure if what I am doing is possible or not but I need to return a value with specific criteria. I need the latest "Revised Date" from the most recent amended version (Occurrence). However, it is possible the latest amended has no updated "Revised Date" field so it should keep the previous revised date.. In my…
-
Sum FTE Totals for all assigned projects
Hello, I am looking for some assistance with summing the total FTE hours per person assigned to a project. We use increments of .05 which is always rounded to 0 in the pivot app. I can't achieve this sum in reports, for some weird reason it won't allow me to group or summarize the Assigned Resource column. Is there a…
