-
Formula for Auto Reference Number
HI cpould it be possible to indicate time hour and minute in a formulated column i have sample formula in here but its still unparsable i able to create and test with some helper columns but still didnt work =IF([Case Type]@row = "Internal Service Issue", "ISI", IF([Case Type]@row = "External Service Issue", "ESI",…
-
How Can I Sum the Price of Multiple (join-collected) Items
I have two columns; one consisting of a list, using Join/Collect, of items, and one consisting of a formula to look up and sum the prices of the Join/Collected items. When the list (column 1) of items consists of only one item, the formula (column 2) works fine. When there are multiple items in Column 1, the formula does…
-
Referencing a specific level in a hierarchical setup
Hello, I am trying to do a count of statuses on a sheet but I need it at the second child level. In many cases there are children below this level also that I want excluded, and I also do not want to include anything from the parent levels above either. I do have a helper column that assigns a number code to each row with…
-
Dates in Timeline View are not the same as Grid/Gant View
Does anyone else have the issue where the dates in the Timeview line (mainly Milestones) are a day ahead of what they are in Gant/Grid View. It seems to be correct for some milestones but not others- I can only assume it's to do with predecessors with/without lag. As you can see on my screenshots, the ones highlighted…
-
Using #IFERROR to display #NO MATCH as a usable automation
I am wanting to use a helper column to display a value, lets say "Not Found", based on if the column "Attending? (from RSVP tracker)" is #NO MATCH. How do I use the #IFERROR command to do this? I can't seem to get my formula correct. I want to do this to be able to use and automation based on people not having a response…
-
Formula to separate first and last name from Contact List
Hi everyone! I'm trying to separate Last Name and First Name from a Contact Column. I'm CLOSE, but it's not QUITE working. I tried to take a formula I saw on another thread, but because I don't actually have an understanding of what the formula is actually doing, I'm unable to trouble shoot why it's wrong. It seems to be…
-
Seeking help merging data from two fields - one column is locked and another is a shared column
Hello, I have received a request from the business to streamline a process and minimize data entry, but I am not confident in how to cleanly execute. Here is the scenario: This is a compliance related activity. Submitter starts the process through a form. All the fields on the form are locked within the base sheet. One of…
-
How do i use OR function within SUMIFS function?
Im trying to use the OR function so i can get the total where criteria is met by one of 4 conditions. Can someone tell me the proper syntax? Below is my current formula: =SUMIFS({Upcoming Jobs (High Level) Range 3}, {Upcoming Jobs (High Level) Range 1}, =[Job Name 1]@row , OR({Upcoming Jobs (High Level) Range 1}, =[Job…
-
#INVALID REF with SUMIFS Formula
Hi Smartsheet Community, I'm having an issue with a cross-sheet SUMIFS formula that works most of the time, but occasionally shows a #INVALID REF error in the sheet and/or reports. To fix this I need to manually open and save the source sheet. The goal is to sum the invoiced retainer amount on the INVOICE sheet using three…
-
Can i use iferror while using index(collect?
Hi, im trying do an index collect but i get an error when there is no value that meets the criteria. Can someone tell me if and where i can use IFERROR in the formula below? =INDEX(COLLECT({Upcoming Jobs (High Level) Range 1}, {Upcoming Jobs (High Level) Range 2}, =[Date in Final]17, {Upcoming Jobs (High Level) Range 1},…