-
Check A Box When A Value On Another Sheet Changes
I have a sheet that is pulling data from a second sheet. What I want to happen is when the status changes to RECEIVING COMPLETE on the second sheet, it checks the box in the received into stock column. The problem is that once that status is updated to that value on the second sheet, it's moved onto an archive sheet that…
-
IF formula for INDEX/MATCH across sheets
Hi, I'm really struggling with this one. I have a sheet that has a drop-down with options for "Yes" and "No" and I want to perform an INDEX/MATCH function based on another cell in that row, but only if the result of the drop-down in that row is Yes. The sheet reference for the Yes/No drop down is {App_S}, and the two cells…
-
If / and forumula
Hi I'm trying to get a formula that returns the following grades. High Distinction (HD) 85-100 Distinction (D) 75-84 Credit (C) 65-74 Pass (P) 50-64 Fail (F) <50 I've tried this a few different ways but keep getting the #UNPARSEABLE message. Appreciate any help with trying to get this formula to work. =IF(AND(Percentage1 <…
-
What am I doing wrong?
Shows #Incorrect Argument set =IF(AND(IF([Jan Scheduling Location]@row = "BPHO|Belen Public Health Office", [Jan Appt Type]@row, "Err1"), IFERROR(INDEX(DISTINCT(COLLECT([BPHO|Belen Public Health Office AT]:[BPHO|Belen Public Health Office AT], [BPHO|Belen Public Health Office AT]:[BPHO|Belen Public Health Office AT],…
-
Nested IF Statements to Determine Priority
Hi everyone, I've written nested IF statements for a Risk Register, looking at Probability and Impact to determine the color. It's been a while since I've done this and I'm obviously rusty. Can someone tell me why the following is unparseable? =IF(AND([Probability]@row = "High”, [Impact]@row = "High”), “Red”,…
-
Is this a bug and if so, how do I mitigate it?
I have a formula to autopopulate a drop-down list field to show one or more items, grouping together a number of related rows in another table. =JOIN(INDEX(DISTINCT(COLLECT({EcoTech Activities Managers}, {Activities PIDs}, =ID@row)), 0), CHAR(10)) Basically collect the names of managers from another table where the key…
-
Dynamic "UNIQUE" Function
Is there any type of function similar to the UNIQUE function in Excel that I can use? I have a Master List on one Sheet (called "QMS Transformations Common List") and I want to use links for the first column on another sheet ("named "QMS Metrics Calculation - Workstreams"). In excel I could use the UNIQUE function, and it…
-
Field Service Management in Smartsheet
Has anyone built a Field Service Management solution in Smartsheet? I need to maintain a database of; store locations technicians, their backup tech and their backup of the backup tech dynamic schedule of availability of said technicians I envision some form of a 'lookup' worksheet where appropriate and relevant data from…
-
Different way to track health
I have a project without a set end date and am being monitored on if I am getting it closer to completion by the % complete field, how can I make a formula that tracks health based on that forward progression so that if my % complete goes up it stays green but if it stays the same for three days it goes to yellow and if it…
-
How can I auto-populate a formula in a column that is referencing values in another sheet?
I have the formula below that is referencing the Planned Date and Exit Shop Date, but when I drag the formula through the column it doesn't update these dates as well? What I want to do is as I drag the formula down a row I also want the Planned Date and Exit Shop Date to move down a row and not remain static Is this…