-
OR function referencing other sheets with Index - returning INVALID COLUMN VALUE
This is driving me nuts. Sheet1: {Sheet1_CC} = Course Code (Text/Number Column) {Sheet2_MRC} = Modify Replace Code (Text/Number Column) {Sheet1_AT} = A Task Dropdown list with restricted options (NEW, MODIFY, REMOVE) Basically a course code can be in either column and I need to pull relating task into Sheet2 Sheet2:…
-
How do I have sumifs equation that tracks if=both contribute new to new and renew to renew
We need to find out how to implement whether the equation is Both and how to associate new + renew.
-
Join(Collect question
Hello, I am trying to join all notes in a cell from another sheet if the phone number on that row matches the phone number on my current sheet's row. I can do this in google sheets but struggling to translate to smartsheets. =JOIN(COLLECT({Notes}, {Phone #}, [Phone #]@row), ", ") This is returning 9 commas for each cell.…
-
MAX(COLLECT Not Returning Anything
Hi! I'm running into an issue using MAX and COLLECT that I was hoping someone in community could help out with. I've got a sheet that collects billing information. Among other things, a sheet for each employee contains a Project ID and a Monthly Report End Date. Each month, new rows are added to the sheet. As projects tend…
-
Combining two data points on one sheet into a second sheet
Good afternoon, I have inherited a smartsheet that I am in the process of updating. In a nutshell, my team fills out a form, and that data is stored on a SmartSheet. My sheet takes that data and reorganizes and reports it. As I said, I am updating it at this time to pull data from the 2023 data points to the 2024 data…
-
If Contains lookup to another sheet result is empty show value from other cell in row?
I have the following formula in a cell that until now was successfully looking up a date in another sheet and when the match of "OSHA Inspections" was true in that other sheet it would grab the most recent date for the shipto id. What I'm trying to do is modify the formula so that when the date field is blank in the…
-
If not blank formula for 2 column options + result as a sum formula or 0
Hello, I am trying to make a formula where if "Drug Selected/ Wrong drug (0)" is not blank or "Patient profile / wrong patient (0)" is not blank then the return value is 0 and if it is blank then there is a formula where it adds up the value remaining columns. Would anyone be able to help me with making this formula? Thank…
-
5 nested IF arguments
=IF([#1 Date of Review]@row = [Latest Review Date]@row, [#1 Time]@row, IF([#2 Date of Review]@row = [Latest Review Date]@row, [#2 Time]@row, IF([#3 Date of Review]@row = [Latest Review Date]@row, [#3 Time]@row, IF([#4 Date of Review]@row = [Latest Review Date]@row, [#4 Time]@row), IF([#5 Date of Review]@row = [Latest…
-
Automate Parent Status Rollup based on Descendants' statuses
Hi folks — has anyone figured out how to enable users to update statuses manually for child rows, and then have the parent rows’ status update automatically? There are multiple levels of hierarchy. For example, I have three statuses that a child row can be; In Progress, Not Started, Completed. I have figured out the…
-
How can I change this formula to exclude holidays?
I have set up a project status report to automate expected deadline dates for certain project deadlines. My current formula excludes the weekends, but not holidays. What can I change/add to this formula to do so? =IFERROR(WORKDAY([CD Kickoff (E)]@row, +1), "") Also, if I need to have the dates counted by 4 weeks vs days…
