-
How to remove the unnecessary commas?
I worked out this formula with someone during a ProDesk session but they weren't sure how to remove the commas when they weren't needed so I'm hoping someone else maybe has an idea! The formula: =JOIN([Inspected Employee's Name 1]@row + ", " + [Inspected Employee's Name 2]@row + ", " + [Inspected Employee's Name 3]@row +…
-
Help with a weekly updated formula
Hello! I'm still a beginner smartsheets user, and while I am decent with excel the live updates is still very new. I'm trying to create a formula that will pull information on a weekly basis, M-F, that updated for each week and didn't pull from the previous week at all. For example in this screenshot, I would like a…
-
Countif with multi values and variations
I have countifs statement but then need to account for multi-value dropdown list and wonder the best way to capture the values. So counting projects status of in project - with a business value of 6 options (multi-value). So Business Value column has multi-value options from a drowndown. I really only care about the total…
-
True count of Children(), not just non blanks
Doing project roll up and trying to use the formula below to roll up a complete status. =IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "Not") However, blank cells do not contribute to the COUNT(CHILDREN()), thus if there are three children and someone deletes all values in 3 cells or any non…
-
Check box if matching value in other sheet
Hi All, I am attempting to write a formula that would check a box if a value is present in another sheet, and I can't figure out exactly how to write it. I am thinking it would be a combination of IF, INDEX, and MATCH. I have two sheets, both of which have a column called "Protocol ID." I want to check a box if the…
-
Lock the cells based on the duplicates
Hello Experts, I have 3 COMMENTS row duplicated and 1st COMMENTS row is having translation captured for the 3 language headers. However, there is 2nd and 3rd COMMENTS row. I have already enabled duplicate logic to highlight the row in YELLOW color, so the 2nd COMMENT row is highlighting in YELLOW. Is there any possibility,…
-
Need a formula that evaluates multiple cells for data
I need a formula that looks in multiple cells for a value, when it finds a value, do a lookup to find the translated value. I have a sheet that is gathering data in different languages, and we need them all to be translated to English. Below is the snip it of the sheet. So if the form is in English, it will return the…
-
Ability to remove a column formula from individual cells for manual updates
Adding a column formula is convenient, but there are times when the formula doesn't apply to everything listed on the sheet. It would be nice to be able to remove the formula just for that cell so it can be manually updated.
-
How to add varying months to varying dates?
I am really having trouble getting a formula to work here. I've gone through a number of support articles, but I'm just missing something. I want a formula that looks at Date Contract Executed and it adds Months Until Expiration to produce a new Date of Expiration. I've got my date columns as dates and the middle column as…
-
Formula to populate just the first row of data in another sheet
I'm trying to write a formula to populate data from one sheet to another but I only want the top row of data to populate and then update when the 1st row changes. I have a Submission Form sheet that gather data from a form, when a new form is submitted it will be at the top of the sheet. I want that top row of data to auto…