-
Trying to create a status column for rental assets
I’m running into an issue where I’m trying to make a column formula return a status of an asset depending on the current date, and the date it was checked out. =IF([Assignee Due Date]@row < TODAY(), "OVERDUE", IF(ISDATE([Assignee Check-Out Date]@row), "Checked-Out", IF(ISBLANK([Asset Name]@row), " ", "Checked-In")))…
-
Formula based on a symbol column
I have a symbol column and want to add a score in another column based on the symbol. I am currently using the following formula but am getting an #INCORRECT ARGUMENT error. =IF([Risk Management]@row, "Up", 3, IF([Risk Management]@row, "Unchanged", 2), IF([Risk Management]@row, "Down", 1)) I've solved it myself now. :-)…
-
IF(AND((VLOOKUP, convert to IF(AND((Index(Match
I am using the below vlookup but my sheet getting too big and I need to convert to an Index(Match). This works: =IF(AND((VLOOKUP(Code@row, {Master List (US)}, 13, false) = "Mandatory Training Non-Compliance"), (VLOOKUP(Code@row, {Master List (US)}, 16, false) = "2020 Q2")), (VLOOKUP(Code@row, {Master List (US)}, 15,…
-
Editing Formula to not include a row- but maintaining everything else.
I have this formula: =SUMIFS(CHILDREN(), CHILDREN($[Recruiter Name]@row), <>"PF External PRN") + ROUNDDOWN((SUMIF(CHILDREN($[Recruiter Name]@row), "PF External PRN", CHILDREN([2020-01]@row)) * 0.5)) I need to not count a row in the child rows called "Lawson Starts" while maintaining everything above as well. How would I do…
-
Using Today function as a start and end date in smartsheets?
I want to use "today" as the start and end dates on a smartsheet (it will be milestone tracking today's date) It seems to work fine when the column is defined as "date" (I can hover over the cell and see the formula), however, if the field is date/time (attached), I don't see the formula - just the current date. How can I…
-
Stopping alerts once completed
Hi all, I'm new to setting up alerts and would appreciate some guidance. I have a list of ~500 people for certification. I have their first and last names and email addresses. They need to add an ID#, click a dropdown choice and enter a date. I've set up an alert to send an alert daily, but how do I stop the alerts from…
-
Formula Help
I need to calculate the NETWORKDAYS in a sheet but also need to make the formula use todays date if the cell is blank. This is the formula that I have now but it is counting weekends and holidays in the calculation and I need it to omit weekends and holidays. =IF(ISBLANK([Booked Date]1), TODAY() - [*Received Date]1,…
-
Automation - Cancel Update Request Based on Conditional Logic
Is there a method to use a workflow to cancel a pending update request? Let's say I have a user who receives an update request through a reoccurring workflow, and rather than use the Update Request form in the notification, they opt to edit the sheet directly. Even though they have fulfilled the function of the update…
-
Issue: When using the Find formula, it doesn't work when trying to find the backslash (\) character
I can get the Find formula to work when looking for a letter but I need to locate the back slash (\) character but when I created the formula I get an #UNPARSEABLE error. Example: The string to be searched looks like this "Noel\Mark\OfficeDesign" or "Abraham\Lee\kitchenDesign" I need to pull the names, the first name is…
-
Referencing a sheet
Hi! I'm wondering if there is any way to reference a full sheet. If you are able to do that and then write formulas that include columns within that referenced sheet, will that work? Thanks!
