-
Month and Year from a Date - #INVALID DATA TYPE
Hello Super Smart Guys! I am struggling with month and year extraction from a date. The date is extracted automatically as raw data from salesforce, please refer screen: Based on answer from this community, I was able to extract Date Only and remove hour, formula used: =LEFT([Date/Time Opened]@row; FIND(" "; [Date/Time…
-
Find if a word (from a list) is contained in a text
I have my table1 with a list Column A : Client (example : EDF) Column B : Salesperson associated to the client I have my table2 on another sheet. The sales person types in the name of the client everyday, but it is sometimes surrounded with other words (example : "Groupe EDF", or "EDF LYON"). I would like a formula to…
-
Formula for auto populate?
In the REP field, every rep is assigned to an area. Would there be a formula that if I choose SALES REP 1 it will auto-populate on the state field AREA 3? Thanks
-
IF formula doesn't work?
Hello, I am using an IF formula to generate numbers but it doesn't seem to work when i have numbers over 10,000 and it just spits the same number back out. See below: the formula I am use is: =IF([column 1]@row >= 6500, "6,500", [column 1]@row) so I'm trying to get it to say if column 1 is over 6,500 then only write 6,500…
-
Index across sheets
I am attempting a complex formula that will link two SmartSheets. (See below for screenshots) SPAR Review Testing: Uses a form to collect document submissions. This sheet has a submission date column. SPAR Reviewer People Matrix: A people matrix that is used as a schedule showing who is responsible for reviewing the…
-
Join(distinct(collect))) Multi-select column
I'm wanting to create a list of Markets for a selection of sites in a multi-select column. Based on other community entries, I'm using: =JOIN(DISTINCT(COLLECT({Site Market Range}, {Site list Range}, CONTAINS(Site@row, @cell)), ", ")) This works when there is 1 selection in the multi-select field but as soon as I add more…
-
I need to result the column's name when using a JOIN(COLLECT) or INDEX(MATCH) formula
Previously, Row 1 of my smartsheet contained the column's name. This way, I could write a JOIN(COLLECT) formula to return the column's name if certain conditions were met throughout the sheet for that row. That worked until we applied column formulas to some of the rows. Now Row 1 can't be the column's name because of the…
-
COUNTIF with AND
I've written the statement but am getting a #INCORRECT ARGUMENT SET error. I'm new to SS, any help is appreciated. =COUNTIF(AND([Opportunity Risk Path]:[Opportunity Risk Path], [Module Approval Status]:[Module Approval Status], ="Conventional", "Not Started"))
-
Calculations and Dashboard Data based on WeekNumber
I've been asked to create a PM tracking system. All the tasks for the year are in one sheet with each task having a row. I a summary section that feeds a dashboard with data showing data points, charts, and reports based on the month and completion status. The colleague came back with request to revise the dashboard with…
-
INDEX/MATCH function across more than 2 sheets
Hi, I have a list of sites and I am try to auto populate the Site type column based on different sheets (Site Index sheet... but each country has one) I have the formula working to reference just Australia's Site Index Sheet but I need to INDEX all of the countries sheets ... =INDEX({AU - Ops Site Index BL}, MATCH([Site…