-
Formula to check the cell for 4-5 text strings
I have a condition where in the formula should check the text in CELL A and return value based on the assigned values and should leave the formula cell BLANK if none of the condition is satisfied. I tried it through spreadsheet and the formula is: IF(OR(A1= "Name A", A1="Name B",A1="Name C",A1="Name D",A1="Name E",A1="Name…
-
How would I return a specific status based on differences in multiple other cell columns?
I am attempting to create an automatic status update that is driven by different options selected in multiple other cell columns. For example: I want my Status column to indicate "Assessment" if another column regarding the assessment is marked as "Yes". However, if another column regarding a meeting is marked as "Yes," I…
-
Formula for totalling specific options
hello! I have a sheet for a menu of options. We are building kits of different swag items and the sheet has 5 different options with different price breaks. I'd like a sales rep to be able to go in and select different options then see a kit price. I was thinking I could add a check box column then pull a report on that…
-
At Risk Flag triggered by upcoming due date and/or past due, and % complete less than 100%
Hi, I'm looking for formula help with the at risk flag. Goal: If target finish date is within 7 days OR past due, and % complete is less than 100%, then activate flag. when % complete equals 100%, flag can deactivate. =IF(AND([Target Finish Date]@row <= TODAY(7), [Target Finish Date]@row >= TODAY(), Status@row <>…
-
Use date and status to define RYG balls
Hey all, Trying to make a simple task management sheet, using today's date and the task status to define a Red, Yellow, Green or Grey ball. If end date is today or later, AND status is not complete or cancelled, green If end date is today -1, AND status is not complete or cancelled, yellow If end date is today -2 or…
-
Trying to pull data into a cell from one column based on criteria from another column
Hello everyone, I've built a project schedule and I'm trying to pull only the task that's "In Progress" into the parent row of that phase. So basically my formula needs to look at the children in the status row, find the one that states "In Progress" and pull the data in the Task column from that same row into another…
-
How do I check for duplicates within several columns and check a box
Hi All, I'm making a grid for staffing events. Basically each event can have up to 3 staff members. I want a formula that will check for if there are multiple events on the same date and if a staff member is accidentally double booked. If they are double booked, to check the checkbox so I can run some conditional…
-
Contact List using DataMesh & Concatenate
Hi, I have added 2 columns to a data sheet, one for Analyst Name and one for the Analyst Manager. I have brought in the email addresses of both of these by DataMesh. I want to be able to apply a current user filter to a certain column which would enable the Analyst and their Manager to only see the rows that are applicable…
-
Not so pretty IF-function (works) but needs makeover
Keeping track on vacation time I need to convert dates to weeks and then again to an overview with checkboxes. In order to get a list of weeks [c_weeks_listed] (later in use with FIND) I have come up with the following IF-formula which works but will become too complex if expanded further. Any ideas for a more powerful…
-
Counting Criteria that Overlaps
I have a multi select dropdown column where users can select phases of a job they have experience in. For this example, let's say they have the options of "Pre-Execution", "Execution", and "Closeout". I want to total the number of users with "Execution" experience. Using "=COUNTIF([dropdown]:[dropdown],…