-
Remove Extra Commas in Join/Collect?
I have a great formula of JOIN and COLLECT together that pulls info from 11 columns. It pulls strings if they meet certain conditions with commas as a delimiter. But if the conditions are not met, I have a bunch of commas next to each other. E.g. , , , EX1, , EX4, , , , and in some cases, just 11 commas. Is there a way to…
-
IF-formula on a date range
Hi guys, For the last couple of days I've been trying to create a simple formula, but in some way it doesn't work. I want to check if the date of today falls inbetween a specific date range (2 different columns), using the IF-formula. I've tried this formulas: =IF(AND([Date 1]1>=TODAY(),[Date 2]1<=TODAY), "True", "False")…
-
Counting Multiple Location Values
I have a metric sheet and I'm currently using a CountIF formula to count the locations in my sheet, works great. But I'd like to take it a step further and I want to group the location entries into APAC, NA, Canada, & EMEA. I'm trying to use this formula: =COUNTIF({ERG MEMBERSHIP EA Location}, "Hyderabad, India",…
-
Gantt - Progress Ahead or Behind Schedule?
Hi All I'm keen to look into options to report on project progress against schedule. Generally we set a project programme, then progressively update %Complete against lines and the project progresses. The progress bar works well as visually indicating if each activity is ahead or behind the current date. I'm looking to try…
-
Is it possible to have a column in a sheet serve as a reference to multiple other sheets?
Here is my scenario: I am redesigning some of my template sets and dashboards but leaving the originals up and running at the same time. I created a sandbox environment (workspace) to do this in. Some of the new sheets use the same data and formulas. Copy and pasting the formulas sometimes required me to recreate the…
-
Trying to do a SUMIF formula - Help!
I've been trying all kinds of formulas and can't get this to work right. I'm trying to do a formula with the below that says IF Approved? column = "Yes" then SUM the range of cells (CSM PM + DEV / BA + Arch/Engin + UIUX +QA)
-
Index(Match()): Lookup a price of a product from another sheet but keep historical entries
Hi am am hoping to lookup a price of a product from another sheet based however keeping historical price changes. Smartsheet A: We have production runs of product XYZ listed in a sheet A. Based on a concat we want to lookup a product price based on the MOST RECENT entry from Smartsheet B. However we want to keep Entry A of…
-
Formula Help with Symbols
I'm relatively new to Smartsheet formulas and I'm having trouble with the following formula I've tried creating to control the symbol displayed based on the Status selected for the row. Here's the formula I'm trying with the #UNPARSEABLE error received... =IF(ISBLANK([Status]8), "", IF([Status]8 = 'Ready for Testing',…
-
Populating a Number Value based upon a CONTAINS condition
I am collecting feedback from a series of clients. I am separating each client by their revenue number to show the satisfaction rating for each "tier" of our client base. As such, I am trying to find a formula which will populate their revenue number value associated with their specific client number. For example, client…
-
Importing from Excel to Smartsheet (Grid) using names for contacts
I am trying to import an excel spreadsheet where I have names in a column on each row. The names are formatted as FirstName<sp>LastName, and they are being imported then the cells are being referenced in other SS grids. I would like them to come in (during the import) as SS contacts (name, icon, email shown and can be used…