-
Updating Date Column if Another Column has been Changed
I am trying to create a formula to change a Date column to update to the current date when another column has been changed. I have used the TODAY function within IF but this updates the current date every time the sheet is opened and saved. I only want the Date cell to change when a specific other cell is changed.…
-
I want to create a formula that would dispute 1 out of 3 message depending upon the content.
I currently have a formula grabbing the data from the CCTV Warranty Expire cell and if it's less than today's date, it's displaying "Out of Warranty" in the CCTV Warranty Status column. If it's greater than today's day, it will display "In Warranty". However I noticed that when the CCTV Warranty Expire column is blank, it…
-
Use of Coloured Symbols
I have two columns that use formulae to calculate whether a Task is on budget based on the hours worked over the Revised Hours. The first column uses RYG balls whereas the second column uses RYG 3-state arrows. The first column displays the coloured symbol but the second column displays the colour as a word. I have tried…
-
Using a formula to Reference another sheet with two variables
I'm trying to create a formula for a report that references another sheet but it needs to reference and check two columns. My crude formula below to illustrate: =COUNTIF ((RefSheet1Range1)="Windows Server" AND (RefSheet1Range2){Status}="Completed") This would return a count of the number of Windows Servers that have been…
-
Help with a SUMIF formula and a reconciliation spreadsheet
I am trying to build a reconciliation spreadsheet that compares two sets of data. Pictured below: The first set of data with the "QBO SKU" is pulling the information correctly using the below formula: =SUMIFS({QBO – shipment history (7/30/20) Range 1}, {QBO – shipment history (7/30/20) Range 2}, Class@row, {QBO – shipment…
-
Ignoring Blank Cells
How do I make this formula ignore blank cells? I want it to total the percent of YES cells to NO in a range. =COUNTIFS(CHILDREN(), OR(@cell = "YES", @cell = "No aplica")) / COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""))
-
COUNTIFS, WITHIN DATE RANGE, ISBLANK FOR ADDITIONAL DATE RANGE
I need to write a formula for counting active projects that are past due for a current month based on schedule complete date being in that current month and when actual complete date is blank. Project Status = Active Schedule Complete Date = 7.1.20 - 7.31.20 Actual Complete Date = Blank Here is the data and fields I would…
-
VLookup is not bring back values
I am using vlookup in a master tracking sheet that is looking up Names and bring back date values in a data entry sheet. The data entry sheet is updated on a weekly basis from the same group of Names multiple times during the week so the formulas is suppose to bring back the dates activities are completed with rows added…
-
What is wrong with my formula?
I'm trying to do multiple IF formulas to show a Green, Yellow, or Red traffic light based on a % complete column. I can get "Red" (<70%) to show by itself, "Green" (100%) to show by itself, but I can't get the yellow range or all three together. What is wrong below? =IF([% Complete]3 = 1, "Green"), =IF([% Complete]3 <1 AND…
-
Sumif in a RYG symbol column
Hello, I have a formula that returns a Red, Yellow, or Green icon depending on criteria in another column. Red, Yellow or Green correspond to a priority in our work order flow. Then, in a metrics summary sheet, I have a Sumif formula based on the status (Red, Yellow, or Green). At times, we need to override the formula,…
