-
"Contains" Cross-Reference Formula
I have two Sheets, one with a list of full course master names (e.g., ABS-435-BL-6W-1D-Master), and another sheet with a list of course sections (e.g., ABA 435 BL 6W). Is there a way to use CONTAINS in a cross-reference formula to check all of the course master names in Sheet 1 to see if any contain the cell value in Sheet…
-
Automate UAT Test Script
I am looking for a way to create a test script using forms. Multiple users would complete the script which would populate a single sheet that could be reported off...... If a tester completes a step in the script (form1), they would submit/save and it would call the next step (form2), each form when complete would save to…
-
Checkbox Column + % Complete Column Formula
Smartsheet I am trying to use this Formula to calculate % Complete based on Checkbox, however the Checkbox is a different Column Called Done, not under the % Column. Can I do that? Existing Formula: =ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100,…
-
Count completed dates once from two columns counting only the most recent competion date by week
I'm trying to count the number of quotes completed by week. The challenge is there are two departments who may work on a quote and each enter their own completion date. Sometimes only one department works on a quote so only one of the two completion date columns is filled in. In a nutshell I want to only count "Type A"…
-
Display Value IF Between Two Dates
Hello, I'm trying to create a formula that will display a specific value if a date falls within a certain range. Any assistance would be greatly appreciated. If “Target Update or Replacement Date” is between 10/01/20 and 09/30/21, then "Budget in Fiscal Year" = “FY 20-21”. If “Target Update or Replacement Date” is between…
-
Sheet Summary - SUM Chilldren Rows based on Status and Dates
Good Afternoon! I am trying to sum children rows where certain status' exist where the Pick-Up Date is today, within the next two days, and in the future beyond that. (Really, 3 formulas). I tried applying the following formula before involving dates to make sure the first part of logic is correct but it is giving me an…
-
Don't include parent rows in checkbox complete roll-up percentage
Hello. I'm new to formulas and need help. I'm trying to figure out how to get the total % complete for a column of check boxes. We need to know the % complete by milestone (which we figured out) AND the total % complete. We use parent rows as headers that should not be included in this total. I had a formula that sort of…
-
Date Range Formula
Hi, I'm trying to set up a formula to count the number of requests that were submitted between a specific date range. Below is the formula that I've written, however I'm still receiving an invalid operation error. Can anyone help me with this? =COUNTIFS({Citi Table Change Tracker Date Submitted} >= DATE(2019, 1, 1), {Citi…
-
COMPARE COLUMNS
Hi have two columns with ID´s and i want to compare them see, if an Id number has a match or a mismatch in the other column. What kind of formula de you recomend to do this? I used Vlookup: =VLOOKUP([NÚMERO DE SINIESTRO]1; {SINIESTROS CF}; 1; false) and Match; =MATCH([NÚMERO DE SINIESTRO]1; {SINIESTROS CF}; 1) But i only…
-
Referencing date and time from another sheet
I am trying to reference an automated creation column from sheet A to populate date AND time in a column in sheet B. I have been successful in the reference, but then have to format the receiving column to date format due to invalid data type. Problem is I need the time to populate as well. Also, when writing this formula…