-
Adding to a X-Sheet INDEX(MAX(COLLECT( Formula
The original formula I had works: INDEX(MAX(COLLECT({Change Log - ADDRESS Prior DATE}, {Change Log - ADDRESS DATE CHANGE}, 1)), 1, 0) Now I am trying to add an IF statement and am getting an #InvalidOperation error. Can you recommend a change to the formula? I am trying to add an IF statement that looks at a date column…
-
Formula to Change Symbol Color Based on % Complete
Hi all, I am trying to have the symbol color change based on the % Complete on a task. I have gotten it to work for 3 colors (Red/Yellow/Green) in another sheet, but now I am trying to do the same with 4 colors, including Blue. I am looking to have symbol turn Green when % Complete is at 100%, Yellow when % Complete is…
-
Pull Attachments from One Sheet to Another
I have sheets that pulls data from the sheets, then fills in a document and attaches it to a row. Is there a way to pull those attachments to another sheet so that all of my needed documents are in one place?
-
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…
-
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…
-
Change "end date" based on status changes
When an end date is used in a dependency setting, you can't use automation or formulas to change it. However, this is when it is critical (pun fully intended)! When someone marks an item as Completed status, it would be helpful if the sheet could update the End Date column to the date it marked completed. For dependencies…
-
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…
-
FORMULA COLORS: Color Correct Part of Formula and Gray from error part forward. Don't Grey it all.
I LOVE that when you are writing a formula in Smartsheet, the formula adds colors to the references as you make successful references to cells. And how it goes "Grey" if there's a problem in the formula: However, It would be VERY VERY VERY VERY VERY helpful if you could keep the PROPERLY referenced cells in color, and ONLY…
-
Circular Reference Error
I am trying to create a formula showing the utilisation of my Project Managers based on hours against projects and PTO vs. their full time contract hours. I am using the following formula for Project Manager 1: =SUMIF(Person:Person, "PM1", Aug:Aug) / 140 When I apply the same formula for PM 2 (substituting PM1 name for…