-
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…
-
Creating a Forumla
Hello, I am needing help with a formula. I have two sheets, one is a sheet where all status's are, the other is where I want the data to live. Is there a way to pull all items with the Accreditation Program and the Complete Status / the total number of items with Accreditation Program to create a % in that cell? I have it…
-
COUNTIF Not Counting Accurately
Good Evening, OBJECTIVE: To count every instance that has been selected with each cell for multi-option drop down Column AGE DISTRIBUTION. I have attempted the following formula's but neither will count all the instances throughout the column or it only outputs #UNPARSEABLE: Thanks taking the time to look at my problem,…