-
Referencing column in another sheet from row 2 only
I am trying to set up a formula that counts the number of cells containing information in the first column on another sheet. I just don't want it to count the text that is in row 1 (ie I just want it to count from row 2 onwards as I am constantly adding data to column). I don't want the formula to even reference row 1 as…
-
Forecast using if function
I am working on a budget tracking sheet where I have a forecast column. The forecast column is dependent on if there is a value in an estimated cost column or actual cost column. If there is no value in the actual cost column, then the forecast column is equal to the value in the estimated cost column. If value exists in…
-
How to return specific Column from a Multi Column Cross Sheet Range
Hi Folks, I have the following formula used to track specific assets: =SUMIF({EventDate_req}, $DateNeeded$1, {Asset2_Range}) This adds up the count in each row in the Asset2_Range that matches the date needed. We created individual one column ranges for each asset (Asset1, Asset2, Asset3) etc. Problem is we are using too…
-
Finding Value in a Cell In Another Sheet
In Sheet 1, I want to COUNT instances of value across a range of cells in Sheet 2, with some cells containing multiple values, separated by commas. Let's say Sheet 2, has the following values in a range of rows: SHEET 2 1: | blue | Group 1 2: | blue, red | Group 2 3: | green | Group 1 4: | red, yellow | Group 1 5: | red |…
-
Automate [Actual Finish] based on when [% Complete] reaches 100%
I can't trust taskees to update [Actual Finish] reliably, so automation seems to be the key. I want to automate [Actual Finish] based on when [% Complete] reaches 100% I have really tried to bang my head through this, but to no avail. I keep running into endless updates of [Actual Finish] or #CIRCULAR REFERENCE issues.…
-
COLLECT in CHILDREN based on another column's value in CHILDREN scope
I am trying to find the min value of CHILDREN in a column of numbers where the value of the [% Complete] cell in the row of that column is not = 100%, but, again, only for the CHILDREN. Using =MIN(COLLECT(CHILDREN(), [% Complete]:[% Complete], @cell < 1)) or =MIN(COLLECT(CHILDREN([Days Till Due]:[Days Till Due]),…
-
IF formula help to gray out a status
Hi Community...having issues with this formula working for the "Gray"Status. The other criteria is working so can't figure out why I can't get gray status to work. =IF([Actual Progress]10 >= ($[Week 1 Target Progress (Aug 22)]10 * 0.8), "Green", IF(AND([Actual Progress]10 < ($[Week 1 Target Progress (Aug 22)]10 * 0.8),…
-
Reference Another Sheet Ranges Shifting
I have a metrics sheet built out referencing a master sheet. The master sheet is a running tracker and each month we add new rows to it. Any time we do this, it shifts my reference ranges on my metrics sheet. We've tried adding new rows to the very bottom of the master sheet as well as inserting the new rows between…
-
Sumif
Hi, I am trying to get the sumif function to work for me and I am having trouble. I am trying to count the dollar amount of invoices in column Invoice Amount if the the JOB# column has "BO" in it. These are the formulas I have tried so far: =SUMIF([JOB#]:[JOB#], IF(FIND("BO", [JOB#]:[JOB#]), [Invoice Amount]:[Invoice…
-
# No Match errors with INDEX/MATCH formula
Dear Community I am having the hardest time debugging some #NO MATCH errors with my INDEX/MATCH formula. I have triple checked and can’t find any reason for things not to match. The rows right above and below work fine. I am pulling in the percent complete into a summary view sheet. I am matching on the site name,…