-
Formulas not carrying down to new rows
I have a sheet with formulas in most of the columns that should be carrying down to newly added rows but after several months of it working correctly, they no longer carry down. I have checked for blank rows and made sure the formulas are correct in each column and present in every row but after working for awhile, they…
-
Traffic light formula
Feel like this should be a relatively simple formula, but can't get it to work! I want to do this: If content due date is greater than 4 workdays from today OR if content submitted is checked return green If content due date is between 1-4 workdays from today and content submitted is not checked return yellow If content…
-
Why is my multiplication formula invalid?
I am trying to create a column that multiplies the total hours by the pay rate of certain staff. In the pay rate column I have used the formula =IF(Name1 = "Sophie", "25.54", IF(Name1 = "Peta", "55", IF(Name1 = "Stephen", "55"))) This formula works well, however if I then try to use those values in another formula is just…
-
Using COUNTIFS to count specific words
I have a sheet with a multi-select containing topics such as: Age, Disability, Wage-Hour, Sex, Sexual Harassment, Sexual Orientation, and other unique values. I am using this formula: =COUNTIFS({Drop1}, CONTAINS(Claim@row, @cell), {Month}, 1) to count specific instances by month and it works except for Age and Sex because…
-
Display negative for past days and positive for upcoming days
I've got a sheet with a "Start" column and a "Today" column. I want this formula; =NETDAYS(Start25, TODAY25) To display the number of days as a negative if the Start was prior to TODAY or as a positive if the Start is after TODAY. Currently, my displayed results are opposite to that.
-
IF Statement w/Drop-down Multi-select
Hi folks, I'm trying to create a formula focused on two columns (one a single-select drop-down and the second a multi-select drop-down). Based on the value in the single-select drop-down, I'd like the multi-select column to include multiple selections. My attempt at the formula is below: =IF([Change Requested]@row = "New…
-
How to Use VLOOKUP and SUMIFS
I have 2 sheets. The first one is for case reviewer #1. It lists the case ID and the hours each day they worked on it. In total I have 3 reviewers and each have their own time sheet. Then, I have a summary sheet that lists the Case ID, each reviewer, and then the total time spent on each case. What I would like to do is on…
-
RYG formula for indicating expense vs. budget
Hi, I am trying to use RYG balls to indicate how much remains in a budget (red indicates 90% spent, Yellow indicates 75% spent) Using this formula, I get green and yellow, but I can't seem to get red, even though there are expense/budget over 90% =IF(Charged14 / [Budget Allocated]14 > 0.75, "Yellow", IF(Charged14 / [Budget…
-
Determining Exact Match for Multiple Criteria in a Multiple Selection Dropdown
I am trying to grade a quiz where the answers were submitted via a smartsheet form. I am having trouble figuring out how to confirm that there is an exact match on questions that had multiple answers needing to be selected to be considered correct. On questions that only use one answer, this is the formula I use:…
-
Return Work Dates between start date and end date
Hi, I have 2 columns, [Start Date] and [End Date] on my sheet. I want to fill another column [All Dates] with all dates between and included start and end date. So if: Start Date - 8/24/2020, End Date - 8/28/2020, i would like the return values in a column [All Dates] to be: 8/24/2020, 8/25/2020, 8/26/2020, 8/27/2020,…