-
Dynamically Calculate Child Counts Based Upon Filters.
Hi All, How do I calculate the count of children in a parent row and have it dynamically change depending upon filtered results. I’ve tried COUNTIF and COUNT plus SUM CHILDREN and they all work showing the correct number of children before a filter is applied. When I apply a filter, the number of child rows reduce, based…
-
Problems using @cell with dates when range contains blank cells
Hi, I've been trying to use this formula: COUNTIF(Due Date, MONTH(@cell) = 8, but it returns an error INVALID DATA TYPE If I limit the data range to exclude the blank rows than it has no problems. But this is not pratical because later when I more tasks, I have to adjust the formula. Interestingly SUMIF does not have this…
-
Link count and Ranges - Does each column in a range count as a link?
Hi Folks, After reading up on the issue of cross sheet link limits, I would like to confirm what 'counts'. If a range is defined as having 10 columns does that count at 1 link or 10? If a range has 20 columns is that 20 links or one? And finally if that same range is used for several VLOOKUP functions in a destination…
-
index/match help
Please refer to the attached image. I would like to create a formula to display just the "Preliminary" cost for "Atlanta - Cumming" on a separate sheet using the index/match formula but keep getting an error. Can anyone help with this? I am open to using another function as well.
-
Multiple checkboxes
Hi there! I have two columns, for the sake of this example we can call them A and B. I would like to create a formula so that if A and B are both checked off, it will show the text "Sent" but if only A is checked the text will say "Reviewing". If neither checkboxes are marked I would like the text to read "Pending". Can…
-
Calculate End Date from "Start Date Plus Number of Days (calculated and rounded up via formula)"
Hello Community! I'm trying to calculate End dates based off of a hard Start date, and adding a number of Days that I have listed in another cell. The number of Days is calculated via formula, from one cell that lists Project hours (which I manually input) and each project is assigned to a crew, which have different…
-
IF Formulas for Status Columns
Hello, I'm looking for assistance in writing an IF formula to change a cell's value based on the value of other cells in the same column. Specifically, the values will be symbols. If all symbols are green, I'd like the first row to show as green, if any are yellow, then the top row should be yellow, and lastly if any are…
-
SumIFs formula #INVALID DATA TYPE error
Getting the #INVALID DATA TYPE error with this formula: =SUMIFS({Forecasts (SS) Hours}, {Forecasts (SS) Consultant}, $[Consultant Name]23, {Forecasts (SS) Start}, WEEKNUMBER(@cell) = 48, {Forecasts (SS) Cancelled}, 0, {Forecasts (SS) Start}, YEAR(@cell) = 2019) What's frustrating is if I change the ${Consultant Name]23 to…
-
Gantt View - Settings requests
Hi, I'm trying to create a dynamic 'To-Do' list (for want of a better phrase), that automatically steps the jobs - i.e. only start task 2 when task 1 is expected to complete. I want to make this as automated as possible, and I understand that dependancy columns do not accept formulas, but wanted to know if there was…
-
Modifying RYG Symbols with Dates and Status
We are trying to create a formula on the Schedule field that will change color to Red, Green or Yellow based on Multiple day and Status selection. For example: If Status is In Progress and End Date is > Today and R - Date is Empty then is Green. IF Status is In Progress and End Date is < Today and R - Date is Empty or a…