-
JOIN COLLECT with OR criterion?
Hello community, I am trying to put together a JOIN COLLECT formula with a criterion whose range is a multi-select dropdown menu. Right now the formula is working when only one is selected from the menu. =JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, Regions@row), ", ") How can I adjust the formula to pull from…
-
Identifying Unique Values from the Duplicates
Hello! I found a SS question asked that was basically what i wanted - https://community.smartsheet.com/discussion/46001/duplicates-can-i-count-the-original-but-ignore-2nd-3rd-etc, but I cannot get those formulas to work :(. I then found another website with a slightly different formula - as seen below - that did not work…
-
Checkbox rollup based on another column
I am trying to create a formula that rolls up an action item checkbox if the action item column in the same row is not null. I found a found this in a different post - =ROUND(IF(COUNT(CHILDREN()) > 0, COUNTIF(CHILDREN(), 1) / (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "N/A")), 0) * 100, 0) + "%" It checks if the actual…
-
Using % in RAG status calculation
I am trying to use % complete to indicate RAG, Percentage complete: 75% and over "Green" Percentage complete 50%-74% " Yellow" Percentage complete below 49% "Red" I am unable to use % symbol in the calculation, it gives me incorrect RAG Kindly advise
-
How to remove NA from Calculation
Hello, I am currently using the formula below to get a percentage complete calculation, one of the parameters has the option for NA. How do I include the NA in the formula to make it so it doesn't affect the percentage? It is the bolded parameter below: =SUM(IF([Paperwork Received]1 = "YES", 0.2, 0), IF([Samples Received]1…
-
Need to look across multiple sheets, to match the name and then return the information within a cell
Hi there, I'll try and make this as simple as possible! I have a finance sheet (Picture 1) where I want to be able to create a formula to pull in the cell value, by matching the name of a School (in this instance), by searching across 9 different sheets, and return the value within a cell. So, for example, School A submits…
-
Help with Column formula for Parent rows only.
Hello, I am trying to implement a Column formula that will only register on the Parent rows. After some research on here I have assembled a formula that I believe should be working, but instead leaves all Columns blank. (Formula works as intended in individual parent rows, but once I apply the formula to the entire column,…
-
Join Collect with text
Join Collect is a new one for me and I am struggling... I am wanting to JOIN COLLECT with results only with BFF PM, listing the contact name/card Master sheet: Current formula: =JOIN(COLLECT({Area Contact Range 2}, {Market Area}, [Market Area]@row, {Title}, "BFF PM"), " , ") Reference:
-
seeking to establish a cross-sheet workflow to transfer a cell value to another sheet when a drop do
Hello, I'm seeking to establish a cross-sheet workflow to transfer a cell value to another sheet when a drop down option is triggered. I have tried the row transfer workflow but it transfers the entire row, when I'm only seeking to transfer a select cell value in that row when triggered. Any assistance would be greatly…
-
Trouble combining two formulas with "OR"
I have the following two working formulas but when I try combining it I keep getting error messages. =IF(ISDATE([Meeting scheduled date]@row), "Scheduled") =IF(OR(Acuity@row = "High", Acuity@row = "Medium"), "Required") They each work on its own by referencing two diff columns. I need help combining this to one formula.…
