-
Retrieving parts of a text fields
Hi there, I have a project identifier column that includes a project code (111-AAA) and project name (client name). Ex: 123-ABC Disney Corp I would like to separate the Project Code and Project Name into 2 Columns, which will result in me having 3 columns Project Identifier, Project Code and Project Name. For the 'Project…
-
#Invalid Data Type
I have a successful formula in a sheet summary that is as follows =AVG(COLLECT([Prototype Lead Time (PO DATE and Ship Date)]:[Prototype Lead Time (PO DATE and Ship Date)], [Target Ship Date]:[Target Ship Date], IFERROR(YEAR(@cell), 0) = 2020, [Target Ship Date]:[Target Ship Date], IFERROR(MONTH(@cell), 0) = 1, [New/ Add'l/…
-
Why do my Index Match fields not show the results on Reports?
I have some reports from a sheet that populates a few fields from an index/match formula. On my report, the data doesn't come through. It shows #INVALID REF even though the data is correct on the Smartsheet. Also, it seems like the report isn't updating when those fields on the Smartsheet are updated. I have some other…
-
How to display unique dates from a sheet (source) to another sheet
Hi, I tried searching on this topic, and found a formula that worked for someone else. =IFERROR(INDEX(DISTINCT(COLLECT({Column 1}, {Column 1}, AND(ISDATE(@cell), @cell >= TODAY(-45)))), [Row #]@row), "") While I am trying to figure how to use it for my needs. My sheet has column name "Visit_Date" with listing of dates My…
-
check column against another sheet of applicants
This discussion was created from comments split from: Using an OR in COLLECT.
-
Importing Excel Files
Hi All! Does anyone know why sometimes it shows " ' " in a cell when an excel file is imported? I´m having a hard time when it happens because VLOOKUP formula cannot find the information ... Thank you & Regards, Camila
-
Referenced Dates Not Recognized for Gant View
I have a sheet that will need to be a gant view, the sheet is populated by referencing the columns in another sheet, 2 of them are date columns, but it will not recognize them as date columns to show in a gant view. It seems I need just the date in a new, not referenced column for it to recognize it as a date, how can I…
-
Countifs where the criteria is something other than 3 criterion
Hello. I have this formula: =COUNTIFS({Status}, Status@row, {CompanyName}, OR(@cell = "A", @cell = "B", @cell = "C")) that is working to count the status where the company name is A, or B, or C. Now I need to count the status where the company name is anything but A, or B, or C. I'm sure this is something simple, but I…
-
option is not blank + assigned to
I'm trying to count the number of cells in a column "CPS Has Referred Member To:" that are not blank, by individuals identified in "Assigned To" in a sheet summary
-
Need to look for $99,000-$249,000 in Metrics Sheet. Can't find formula error
Need to look for $99,000-$249,000 in Metrics Sheet. Can't find formula error. =COUNTIFS({1. Case Queue Range 6},>"$99,000",<="$249,000")