-
Help with an INDEX(MATCH(MATCH( Formula
Hey all, I have two sheets: Business Consult and Project Proposal. I'd like to move specific info from that sheet onto the Project Proposal sheet using a unique identifier, the Business Consult ID. I've mirrored columns on both sheets and they are in the same order (though Project Proposal sheet has additional columns). My…
-
Formula for last rolling three months for each service listed
Hi all, Ive deep dived into finding this formula and while it seems to be working, its only counting "0" which is inaccurate. This is a calculation sheet pulling from master tracking sheet. I want it to count the number of rows for each service in the last rolling three months. Thank you in advance!
-
Alphabetize Using Formula
Is there a way to easily alphabetize a list using a formula in smartsheet? In Excel, I would use the simple formula shown below to assign a numerical value to each unique list item. =COUNTIF([Unsorted Range],"<="&[Specific Cell Value]) This formula doesn't work in smartsheet, so I'm trying to find a solution. I am working…
-
Formula for rolling up children status to parent rows
Hello, I am trying to simply roll-up my children status' to their parent row. When using this formula: =IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress")) I receive…
-
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…