-
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},…
-
Conditional Weighted Average
Hello Experts! I see AVGW but I need an average formula to use with an If. Is there such a formula? I have this formula that works great for an average but is not a weighted average. '=AVERAGEIF([Task Name]:[Task Name], "«SPECIFIC TEXT I SEARCH FOR»", [% Complete]:[% Complete]) Anyone have a solution?
-
Formula/Function Help
Hello, I currently have a grid listing different project expenses. I have columns that show CapEx, OpEx, and Customer Funded. I put formulas in the sheet summary to show the total cost of all CapEx, total cost of all OpEx, total cost of all Customer Funded, and then the total cost between all three. All formulas are coming…
-
Can you red flag duplicate information in a report?
I have 12 regional sheets for a client that contains hundreds of data rows. Each row has a specific PO number from a client. Sometimes the client will mis-type a number and a duplicate PO is transcribed into my Smartsheet. Is there any way to red flag that a duplicate PO number has been entered?
-
Formula to get the last child row and apply conditional formatting
I have a sheet conditional formatting has been applied so that Ancestor = 0 is one color, Ancestor = 1 is another. Ideally, I'd like to apply conditional formatting so that all rows with Ancestor = 2 are conditionally formatted to the automatic format style except for the last row which will be a different color. I'm still…