-
Need to return the first NON-BLANK value if [Client full name]@row matches {Client full name}
I have a MASTER sheet containing client data, and an UPDATE sheet to use for processing updates to client data (i.e. users submit data updates via a form and the latest entries go to the top of the sheet). To ensure my master sheet contains the most up-to-date client data, I am trying to use an INDEX/COLLECT function to…
-
Using IF/OR statement in a formula
Hi, I'm trying to have the status on Row 9 update to 'Complete' if either Row 10 or 11 have a status of 'Call - Scheduled' but I am getting an error. =IF(OR([Status10]="Call - Scheduled", [Status11]="Call - Scheduled"), "Complete") Thanks!
-
Add Months to Date formula?
I've read through a lot of the help topics and others' questions, but haven't been able to find a simple solution to my issue - which I think is fairly simple. I'm trying to add a certain number (Varying) of months to a start date to produce an end date. I have a start date column (which is a date column), a column that…
-
I am having trouble using "And", "OR" & "Countif(s)" to build a formula.
Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…
-
Row Lookup
I have multiple project sheets with row items that are the same on each sheet and each row has a percentage of completion. I am trying to create a new summary sheet that has each row item as a column and return the percentage of completion. ie: row items "excavation, framing" etc. are column headings on the new summary…
-
HAS exact match within multiselect - Numbered Values
Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…
-
Populating Dates Based on Drop Down
I am trying to have a date populate based on something that is selected from a drop down. The [column 13]6 is a date. When I just put in one "IF" formula it works, but when I put in multiple IFs I get "# Incorrect Argument Set =IF(Details@row = "1 O&D", [Column13]6, "", IF(Details@row = "2 O&D", [Column13]6 + 1, "",…
-
Assistance with IF formula
I'm working through the Capstone for the Core App Certification, and I'm stumped on one of the exercises. Activity: Use a nested-if formula to automate the RYG status balls in the "Status "column, depending on the "% Complete" for each request. If a request is less than 70% complete, turn the "Status" column into a "Red"…
-
Help with date calculation formula
Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…
-
Pull multiple IF THEN statements from data sheet?
I have a data sheet with 4 Columns that I'd like to pull into a second. In this simplified example, the Data sheet shows phase, activities which repeat for each phase and a date. I also have a column with "Yes/No" if I need the date in the Smartsheet. What formula do I use to get the data to my second layout? I'd like it…
