-
Help with IF AND formula with cross sheet reference
I am trying to write a cross sheet formula, and I keep getting an #INVALID OPERATION error, and I cannot puzzle it out. My current formula: =IF(AND({Scenario REQ ID} = [REQ ID]@row, {Scenario Status} = "Pass"), 1, 0) If the REQ ID from my scenario sheet matches the REQ ID of this row, AND the Status on the scenario sheet…
-
Recording Most Recent Step in a Process
Let's say I have a three-step process that I perform many times: Draft, Review, Publish. (This is a simplistic version of more complex repeated processes.) For each step I have a checkbox column. For each iteration, I create a new row in SmartSheet. I would like to see the status all current iterations in Card view, so I…
-
Import Hyperlinks via Formula (not via Cell Linking)
Hello, Hyperlinks do not import when I use a formula (e.g. INDEX/MATCH, or INDEX/COLLECT) to locate & import a targeted cell. The text in the indexed cell is imported, but not the hyperlink. I am aware of Cell Linking, which does import the hyperlink. But it is burdensome to apply Cell Linking to all the cells in my sheet.…
-
Cross Sheet Reference stopped working
We currently have a Pharmacy Site API reference sheet that is updated daily at 2am PT. Since its a complete rewrite, the sheet is deleted then generated every morning with the new updated site information. This master sheet is indexed across several hundred Smartsheet solutions within our company. I have a specific sheet…
-
Roll Up Calculation using separate sheet
I started with a template, but our resource management is a bit more complex than the template, so I am making adjustments to accommodate for our roadmap requirements. Of course this meant digging into the calculations. The template uses two sheets, PTO & Project Staffing + Staff Resource Rollup. The Staff Resource Rollup…
-
How to automate or move cell values when a date is in the past week?
Here is a tracker of a 10-weeks look ahead of number of hours to spend in project from Monday-Friday which are highlighted in blue cells. The Start Week and End week changed if a date is based on current week. Which means 9-Jun-2024 will be the current week by next week , correct? The problem now is the no. of hours is…
-
Can Someone QA my Formula?
I'm attempting to create a formula the matches the filter conditions that I have on this report: I want to count the number of rows that meet these criteria (count if (this or this or this or this) AND (that or that)) =COUNTIFS(OR([All Results Expected]:[All Results Expected] <> "", [Corrections Expected]:[Corrections…
-
IF Function With Multiple Drop Down Selections
I am not sure how to "Nest" if functions for my application or if I need to use the "AND / OR" functions with it. Essentially, when my field team updates the [Field Status] column I want the appropriate colored circle to update in the [Risk Status] column. Red = Delayed Gray = Not Started Yellow = In Progress Green =…
-
Check box if multiple conditions are met across 2 sheets
Good morning, I have 2 sheets Sheet A: Job #, complete(Checkbox) Sheet B: Job #, Reason, complete(Checkbox) The goal is to have a formula that will check the box in the complete column on Sheet A if the Job # matches between the 2 sheets, the Reason column on Sheet B is "Screens", and the complete column on Sheet B is…
-
Formula to check a box if the date in another column is the current month
Looking for a formula to check a box if the date in another column is the current month. Needing this to set up a process in which I can filter to Month to Date