-
Having overall sheet health based off of parent and child row?
Hi - I know this has been asked and answered similarly, but I am struggling with actually implementing the formulas. I have created a simple sheet that I would like to have a visual R/Y/G/Gr marker at the top of the sheet that is a roll up of the all of the parent rows below. I am basing the formula structure off of this…
-
Nesting ISBLANK within IF(AND) Statements
Hello, I have successfully established a formula link between two date columns (Start & Finish) and my 3 status trackers (Completed, In Progress, Not Started). The formula I have used successfully is: =IF(AND(Start@row > TODAY(), Finish@row > TODAY()), "Not Started", IF(AND(Start@row < TODAY(), Finish@row > TODAY()), "In…
-
How can I stop my Sheet Summary from changing the location of a cell when form data comes in?
I am creating an inventory form that populates data into a sheet on "Top of Sheet". I want to create a summary sheet that reflects how much of an item we need vs what we have entered on the count (via the form). My issue is, whenever a form gets filled out, it also shifts the cell I am trying to reference down. I want it…
-
How to ignore error msgs when calculating the average?
I'm trying to calculate the average of the "planning notified -QSTO generation" column and ignore the invalid data type. I've tried the attached averageif formula but it seems to be not working! getting invalid data type. Highly appreciate your help! Thanks =)
-
Remove negative (- ) sign from a day count
Hello Community! I am trying to write a formula to calculate net work days between estimated and actual completion dates, in this formula l have a condition to show as blank the Design Completion Days cell when there are no estimated/actual design completion dates. The formula is working but is showing the "-" sign, l…
-
Nested IF formula resulting in #INVALID DATA TYPE
I am working on a formula to indicate whether a vendor is approved for work and needed to add another condition and a new status. It is working for the most part, but a few rows are pulling #invaliddatatype and I cannot see how to fix it. What I want the formula to say: If the [Agreement Expiration Date] was last year and…
-
Help setting up a formula
Hello there, I'm trying to set up a formula that shows when a vendor is on holiday. We have a sheet shared with our vendors where they add their time-off, and another one where we send them work. What I want is to create a formula that pulls that data from the holiday tracker and shows if the vendor will be available to…
-
INDEX Function to return an entire Column
Hi. Looking for help using the index function in Smartsheet. In Excel I would use an Array index, notating my row_num = 0, in order to identify the column of the range that I want to return, but Smartsheet gives me a #INVALID COLUMN VALUE error. Any help much appreciated.
-
NETWORKDAYS Formula using TODAY() not returning expected results
I'm using a Formula to calculate the pays past due from today. The current formula I'm using is =NETWORKDAYS([Due Date],TODAY()), but the results aren't coming out as I would have anticipated. 9/29/2021 returns 2 days past due 9/30/2021 returns 1 day past due 10/1/2021 returns -2 days past due So, I created a formula that…
-
Linked sheets & inserting columns
Hi all I am trying to build a compiled set of project "master reporting" that pulls data in from up to 100 individual project sheets. The issue I have is that if I want an additional column to be added into both levels, I need to add to each individual project, then relink each individual project in the "master reporting"…