-
Project Phases of Development
Hello! I am trying to find a way to show the development stage of a project so it can be rolled into a report. Basically if all of the tasks in a section are complete, then the development stage would be the name of the next header task. For example (see image), if "Project Planning," "Drafting," and "Developing" are all…
-
Cross-Sheet Formula Help
Hello, I'm trying to create a cross sheet formula that looks at the content of multiple columns in a row on one sheet and looks to another sheet to confirm those values are present, and then returns a value in that sheet. I've tried VLOOKUP and an IF statement, but I'm not able to get it to work properly. FYI - within each…
-
How to count "Contact" cells with multiple contact per cell
There is a contact column where multiple contacts per cell are allowed. How can I count the total number of rows that contain a specific name? Is it possible to combine COUNTIFS & CONTAINS? =COUNTIFS({Responsible}, {Status}, "NEW", {Responsible}, CONTAINS("Dmitry Gorobets", {Responsible})) The formula returns 0, but the…
-
Formula to keep Date when another date column is blank
Hi, I am trying to create a formula that will keep the existing date in the cell if another column is blank and if there is a date then I want to add 365 days to the other column and return it in my NEXT CONTACT DATE cell =IF(TODAY@row, "", [NEXT CON TACT DATE]@row, IF(TODAY@row, TODAY@row + 365)) My Column Headers are:…
-
UPPER formulas
Is there a way to set a formula so all text in the column or sheet is upper case even if someone enters it on a form in lower or title case?
-
SUMIF/AVERAGEIF across rows not working
I'm trying to SUMIF and AVERAGEIF across rows in my sheet, and I keep getting an #INVALID VALUE error. My table of data includes columns across for each employee, and each row contains the employee's hours worked. The screenshot below shows the helper row (row 3 in my sheet) that specifies whether each employee is a…
-
COUNTIF using Less than Today
I am struggling with a formula that is trying to count the Record Type entries in the reference sheet that meet the following conditions: Record Type = "TEST" Deadline is less than TODAY Assigned ISBLANK I tried many variations of the following formula but it keeps erroring out with INVALID OPERATION. When I parse it out,…
-
How to conditionally format a row if a hyperlink to another smartsheet is included
I would like to highlight rows that do not include a hyperlink to another smartsheet. I thought it may be best to create logical formula in a hidden column and then conditionally format anything that is showing as false (0) in formula is highlighted. Can anyone let me know which formula can work?
-
INDEX MATCH working on one sheet but not another
Hi I have set up an example sheet with an index match formula that looks up an invoice to see if it appears on another sheet. If it doesn't it will bring back the value "New". This formula is working on the example sheet but when it is copied to the master sheet, it doesn't work . See screen shot: Example sheet: Master…
-
Mark an old submission
Hi, I have a sheet I created where a name and status are being inputted through a Smarthseet form. I am trying to figure out a formula to mark old submissions. Meaning if someone submits a name with the status of "Onboarding" and then submits the form again with the status "Hired", what formula can I use to mark the old…