-
Nested If formula help
Below is the formula I am attempting to use. It works perfectly if I my Auto Pay row is No or when the due date has not yet arrived but it doesn't put in the amount in the due row if the date has arrived. What do I have incorrect. =IF([Auto Pay]@row = "yes", IF([Due Date]@row <= TODAY(), Due@row, 0), SUMIFS({Amt Pd}, {Bill…
-
Count If for Workdays
Hello, I have a sheet that has a column with a bunch of different dates on it. I'd like to easily be able to visualize how many of those dates fall within the 1st week of the month, 2nd, 3rd, etc. I'd like to organize it by actual workdays rather than dates. Any suggestions for how to display that data? One thought is to…
-
Question Regarding Multiple Forms.
Hello All, Is there a way to link multiple forms to 1 row? I assume it would need assigned number to reference For example (New Entry into Row 1) Form # 1 would require data inputted in columns 1,2,&3 - populates in row # 1 when submitted ( Work Order Number generated automatically ) Form # 2 would require the work order…
-
Workday and VLook up
Hello, This formula works, but it gives me an exact date (my referenced date plus the number of days referenced via the VLOOKUP). I've tried to incorporate the Workdays function without success and wonder if someone else might be able to help me. Here is the formula that works: =([PCD Draft 1 Start Date]@row +…
-
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…
