-
How to calculate a rolling actuals plus forecast to create an Estimate at Completion
Hello, I am trying to create a calculation that will give me an Estimate at Completion where it sums actual hours/cost up to today with the sum of forecasted hours/cost through the end of the project. I am struggling to get the date to be dynamic so it updates each week instead of having to manually adjust the columns that…
-
Nested Criteria on COLLECT
I'm trying to get an income by multiplying quantity with Price. Price is located on another sheet so I have to find it based on type, route, and active date. Here's the formula that I have: =IFERROR([Economy QTY]@row * (INDEX(COLLECT({Price Range}; {Type Range}; "Economy"; {Route Range}; CONTAINS(Route@row; @cell); {Active…
-
Best formula for a complicated desired result?
Hello, I have tried many different formulas and even tried combining formulas but can't seem to get one that works in a way for syntax to be used as a column formula. Any help would be appreciated, and I thank anyone in advance for tackling this. The desire result is a formula that can be converted into a column formula…
-
How to add a prefix to a project ID
Hi Community, I am trying to create a project ID column that generates a prefix based on the project type. I'd like the numbers to go in sequential order for each project. Ex: abc001, abc002, def001, abc003, def002 Additionally, I would like for it to only generate a number if another column specifies "New Project." Any…
-
I would like to make an IF index collect
I am working on creating an IF(ISBLANK({cross sheet reference of column}), " ", Index(Collect({ NAME},{cross sheet Helper column}, helpercolumn@row,{cross sheet status}, "Status, {CrossSheetDate}, Contains("/",{CrossSheetDate}),1) I get an error for the contains part and the formula works without the date, but I am trying…
-
How can I get my formula to return a phrase instead of #INVALID VALUE
I have a sheet that lists job openings. There is a column formula that assigns a "status" for each job by searching another sheet of applications. For example, if a job has 3 associates applications, and their statuses are "New" "Interviewing" and "Offer" it assigns that job a status of 'offer" because that is the furthest…
-
Copy paste formula only on many sheet
Hi, I have a lot of project who has exactly the same column with exactly same formula in each. Unfortunately, if I want to change the formula for a column, I have to do it sheet by sheet. Is there another way to do it?
-
Formula not working consistently
Below are the formulas I am using to get the follow up date to automatically calculate Final row shows incorrect date at times and sends Automation reminder a day early. The sheet is opened everyday Monday through Friday and has changed while the sheet was open from the correct date to a day early and back to the correct…
-
On an Index Match, why is it returning a match when the match criteria row is blank?
Hi, I am trying to get a a person's region included in a sheet by matching their name in the Staff Column to the source sheet. However, when the row in the Staff Column is blank, I am getting a return from the first line of the source sheet. Is there a way to get the return to be blank if there is nothing to match in the…
-
Order of client health based on 2 columns
Hey Everyone! I am looking for assistance on the best way to do this. What I want is to prioritize our clients whos health is at the highest risk so we can plan to focus on the top "problem clients" This is my conditional formatting if you need it Is there a way to list(numerically or whatever way would be best) worst…
