-
Write a formula that checks whether or not the cell is in a parent row, then performs the following
Write a formula that checks whether or not the cell is in a parent row, then performs the following calculation based on this information: If a task has children, sum the children. If a task has no children, subtract [Extra Column] from [Status]. Write the formula in the [Numbers] parent row, then drag the formula down to…
-
Is there an easier way to automate multiple columns from a single multi-select dropdown?
One of my teams uses a sheet to track to the status of products in every state, with a column containing a status dropdown list for each state (Requested, In Progress, On Hold, Completed, etc.). We recently implemented a form for our internal clients to submit requests. As part of this form, we have two questions…
-
Duration v actual time
Is there a way to add a task that might be 4 days over a one month period, for example. So I don't have to create a separate task for every time that task happens, I just want to show a task that runs from 1 May to 30 May but takes 4 days to complete? I have considered different options, including changing the appearance…
-
Auto scheduling tasks/recurring tasks NOT WORKFLOW!
I can see "recurring tasks" has been asked for by the community for years, but I can't see it has ever been developed, I'm hoping it has and I just can't find it. I want to be able to automate the following: A bi-monthly task that must take place 2 months after the previous task, so that if task one gets moved, all…
-
Using Index Collect formula when there are multiple matches in the source sheet. (#Invalid Value)
Hello, My goal with using INDEX(COLLECT)) was to obtain information based on 3 different criteria, but in my source sheet there are multiple matches for the three criteria specified. What do I need to add to the Index, Collect formula to overcome this or do I need to use a different formula combination? Below is the…
-
Date Format sometimes incorrect
Hi Firstly I am using English formatted dates DD/MM/YY (because the US format is wrong and terrible) I have a sheet with dates in it. I have another sheet that points to these dates. If my source dates are 33/12/2021 or similar they work correctly If my source dates are for example 06/12/2021 then they appear incorrectly…
-
Total sum of results of a formula?
I have a formula to calculate payouts as follows: =IF(AND([Estimated New Annual (ACV) Net Booking $ Amount]@row >= 75000, [Estimated New Annual (ACV) Net Booking $ Amount]@row <= 250000), "$2500", IF(AND([Estimated New Annual (ACV) Net Booking $ Amount]@row >= 251000, [Estimated New Annual (ACV) Net Booking $ Amount]@row <…
-
Conditional Formatting with due dates
Hi - I'm trying to set up conditional formatting within individual cells. The sheet will have a list of projects and a project completion due date. I'm trying to set up conditional formatting specific to each individual due date. Each project will have it's own row and one of the columns will be the due date column. Is…
-
Red Flag for Overdue Tasks, without also Flagging non-dated ones
We are trying to use a formula to do the following: •tasks that have been cancelled are gray •tasks that are past their due date are red •tasks whose due dates come up within the next 5 days and are not yet complete are in Yellow •tasks which are already completed or whose start date hasn't hit, and are due in 14 days are…
-
Combined IF, AND, & OR Statments
I am having trouble with a combined IF, AND, & OR formula. The formula is to check five columns for a Yes "Y" value. For two of the columns only one value has to be Yes. If all are true then return a 1, if not then return a 0. Where is my formatting wrong? =IF(AND([SPC Received]@row = "Y", OR([SDS Received]@row = "Y",…
