-
Metric Sheet Calculations for a Dashboard
Evening All, i'm looking to see how I can add to this formula a rule to not include sales variances of 100%. This shows when I am comparing year over year sales and the site does not have those figures to compare. =IFERROR(AVG({Sales Variance}), "") Many thanks, Sarah
-
IF statement with child/parent logic
The Problem: I need to have autonumber as a Unique Identifier for each row. When that row changes due to changing business requirements, I want to create a child row but it should not have a different Unique ID from the parent. It could either have the same Unique ID or no Unique ID, but if I have autonumber turned on then…
-
Row number calculations as part of a formula
Hi there, I'm trying to replace the value of a row number in a formula. I have created a rowID column so each row has a row number in that column. I would like to make calculations from that to replace the row number part of a cell reference with calculated value i.e. =Status123 i want to be =Status(row#+Y) How do I…
-
How to avoid #REF errors when referenced cell is deleted
I am trying to make a Sheet Summary field that always references the first row in the column (aka ColumnName1). But if I make that reference and then the row is deleted, the reference breaks and gives a #REF error. How can I make a formula that will always reference the first row in the column regardless of what happens in…
-
HELP with IF formula and cell reference
I would like to check cell [Actual Finish Date}4 for a date before executing =IF(ISBLANK([Planned Finish Date]4), 0, IF([Planned Finish Date]4 < TODAY(), 1, 0)) which sets a FLAG to true. If [Actual Finish Date}4 has a date then set FLAG to false. If there is no date then execute on the above formula.
-
Master Sheet - Exceeds Row Limit
I am struggling to figure out how to have a "master" listing of customers that can be referenced by various sheets via INDEX/MATCH cross sheet formulas when that list is too large. Here is my background: I have a master customer listing of 6200 customers. I use DataUploader to upload this into 3 sheets due to size…
-
Filter suddenly not working
Hi All, I have an issue of a filter not working that previously worked fine. The filter looks for cells in a column formatted for text/numbers. Here's an example of the data in the column: NLOND_3104-1 NLOND_3709-2 -2 -1 -2 NLOND_5612-1 The filter looks for data that contain -1 and another filter looks for data that…
-
Formulas for current month and previous month
Hello all (See attached screenshot of my sheet) I need some help. I have a budge/cashflow sheet. From this sheet I would like to create a Dashboard Role-up sheet that gives me the following: PREVIOUS month's budgeted income PREVIOUS month's budgeted losses PREVIOUS month's actual income PREVIOUS month's actual losses…
-
Capture date when row changes
Hi, I have a status column in my row that changes from "animating" to "approved" and would like the start and end date columns to update when status is changed. ie: 1. When status column changes to "animating" - start date needs to capture the date(fixed value) it was changed to "animating" 2. When status column changes to…
-
AUTOMATE RYGB
Hello All, I am trying to automate the blue ball based on complete and the red, yellow and green based on date. I have come up with the following formula but I am getting an UNPARSE any help would be great. =If(Status1 =Complete, "Blue", IF(OR(ISBLANK([End Date]1), [End Date]1 >= Today(-5)), "Red", IF([End…