-
VLOOKUP is data type of TEXT instead of DATE - sorting is off
I am currently creating an report which looks up dates based on specific milestone tasks over a number of project sheets. When the vlookup calculation is entered and I hit the Return key, is it giving me an alert that the value is not in date format. Being the admin, I am able to override this. But when sorting, it sorts…
-
Index and match multiple sheets
Hi, I have an index and match formula set up for 1 sheet and it works great but how do I handle it if I want the index to look at data from multiple sheets to pull the correct information. My current formula is: =INDEX({Site Number}, MATCH([Site ID]62, {Site Name}, 0)) I would like the index to look at the site number on…
-
Using One Checkbox to Check Another
We have a task sheet that uses 3 checkbox columns (Sent, Received or Done, N/A). We have a formula that counts the number of tasks and how many are completed based on the Received or Done column. We are trying to make it so if the N/A box is checked, it automatically checks of the coinciding Received or Done box. Please…
-
Fighting an IF formula
I'm not sure what I am doing wrong. I am trying to get a response for "if the issue date is more than 10 days from the begin date AND the task is not 100%, show Late. Otherwise show 0. I will then be writing a report that shows only late. =IF(AND(NETWORKDAYS(Begin1, Issue1) > 10, "Late", "0", [%Complete]5 < 1), "Late",…
-
N/A Column leads to #DIVIDE BY ZERO
I'm working on an onboarding tracker where I'm using an "N/A" checkbox in order not to count the modules that individuals aren't required to take. I'm using another checkbox to represent "Done" and the percentage complete - =COUNTIF(CHILDREN(Done16), 1) / COUNTIF(CHILDREN([N/A]16), 0) If an entire module is chosen as "N/A"…
-
Issue with CountIf not correctly calculating
Hello, I am using the formula =COUNTIF(CHILDREN(), 1) + "" to count the total number of checked check boxes. However, The number displayed is 0 when I can clearly see the box is checked in that column. For other columns it displays a number but not an accurate count of checked boxes within the column. Is there a different…
-
Using COUNTIF and SUMIF with dates
Hi everyone, I need help understanding how I can use dates in COUNTIF and SUMIF formulas. I have all other parts of the formula working but as soon as I try and add a date condition at all it fails. What I need is something that makes it include all rows where the date in the column Date Closed is the current month. My…
-
Simple formula to show if two cells match- what am I doing wrong?
Hey everyone, I am having trouble with what I thought would be a simple formula: I have a sheet that is drawing information from two different sheets via cell linking and a SumIf formula: Total Order Cost, which is linked to one sheet and Invoice Total, which is summing all cells that match my primary column, which is…
-
Status Change if Column Checked
Hi everyone, I need some help with a formula that just doesn't want to work for me. Currently my status column changes according to the {Proof Approval - Kim} column. It works fine (formula shown below) Now I want to add on to this formula so, if the column {Out For Approval} is checked, I want the status to change from…
-
If/Or formula wit a Checkbox.
I need to color a ball yellow if the value in a column is between .05 and .15. If the value in the same column is greater than .15 OR a checkbox is selected, I need the ball to be red. Not sure how to accomplish this. Appreciate your help.
