-
Auto-calculate a date but round to nearest weekday
I a creating a smartsheet that is calculating several milestone dates based on a single event date. This is the formula I'm currently using to calculate a milestone date 8 weeks prior to the event date, based on the event date being manually entered in the [Milestone Date]16 cell. Is there a way to make this return the…
-
formula for past date
Is it possible to use the same formula but different length of time in the past? IF(AND([Quote]@row <> "Completed", [Date Sent]@row <= TODAY(5)), "Green", IF(AND([Quote]@row <> "Completed", [Date Sent]@row <= TODAY(10)), "Yellow", "Red")))) If today is 04.15.2024 and I need the symbol to change color as days past...…
-
MAX(COLLECT({Range 1}, {Employee Range}, @cell = Employee@row))
This discussion was created from comments split from: INDEX MATCH with MAX.
-
How do I automate a cross check between two Employee Name columns that are formatted differently?
I've inherited a large set of sheets that were set up to pull in employee info based on an employee ID number input via form. There is an INDEX:MATCH to display the employee's name, supervisor, position, etc. from an agency roster based on the ID number. Because some employees mistype their ID number, the wrong info (or…
-
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