-
Why won't my COUNTIF formula not count anything from source sheet?
Hi all, I currently have a system for three spreadsheets: 1) Masterlist for open/closed actions that feeds a 2) Open Actions sheet, and a 3) Closed Actions sheet. The formulas for the first two sheets work perfectly, however, when it comes to the third sheet (Closed Actions), my COUNTIFS formula will not count anything on…
-
How do I modify a 24-hour helper formula to include non-numerical values?
I have a column with AM/PM times set up as a single select drop-down list. In order to make this column sortable, I created a helper column that converts the AM/PM times to 24 hour times. The formula in the helper column is as follows: =VALUE(IF(VALUE(LEFT([Requested upload time]@row, FIND(":", [Requested upload time]@row)…
-
Vlookup formula help
Hi, I am doing a VLOOKUP in which the formula looks up a list of holiday dates listed in a range from a second sheet. If matching, it is to display the second column which identifies the type of holiday (note, in the formula below I have "false" I also tried "true"). The columns sources are both type: date. the formula:…
-
Multiple IF statements HELP!
The request is to check L2 and if it is NAME1 OR NAME2, then look at L3 to return the contact. Trying to return a contact but need to look at two IF's to make it happen: I currently have: =IF([L2]@row = "NAME1", VLOOKUP([L3]@row, {POC}, 2, false), VLOOKUP([L2]@row, {POC}, 2, false)) and I need to add IF([L2]@row = "Name2",…
-
Simplify Formula
I have a formula that checks to see if a date falls between dates. If so, it marks the row current or previous. The goal is to see if the date falls in the current year or the previous year and then label the row accordingly. The formula references another sheet where the dates are calculated. It has a column for the start…
-
INVALID DATA TYPE error when using MONTH()
Hi, I'm working on a formula that will use a date to reference a value in another sheet: The sheet that will be referenced looks like this: I'm planning on using INDEX and MATCH to get a date value from a row in the first sheet to return a percentage value (based on the month that row occurs in) from this sheet. The…
-
Workflow email content based on conditions/critieria
Hi All I'm very very new to SS (just got a license today) and I've been included in a team to improve the notification process to our wider audience with key information to help them complete actions quicker with minimum TLDR! Is it possible to take the column header and add the text to the output notification (email) if a…
-
How to combine multiple IF statements into one?
Hello! I have 3 different IF statements in 3 separate columns that I would like combined into 1 if possible. The three IF statements/columns are the following: Red Status - if task is in the past and not checked complete: =IF(AND([End Date]@row < [Today Helper]@row, [Task Complete?]@row = 0), "Red", "Green")=IF(AND([End…
-
Calculate Date based on Criteria in Two Cells
I need to write a formula to check a cell for a size (Small, Medium, or Large) and calculate a start date from a go-live date in another cell. If the size is small, it's 14 days earlier, if it is medium, it is 21 days earlier and if large 28 days earlier. I've go this as my starting point: =IF([Size]@row,…
-
Can I setup conditional formatting based on a time function?
Hello! Is it possible to setup conditional formatting based on a time function? For example, I want a cell to turn red if it hasn't been updated by 12noon. Or, I want a cell to turn red if the current time is after 2pm.