-
formula building up lead scorecards
Dear all, I have a sheet "lead management" with columns like "number employees", "sector", "region" and many others - all of them as dropdown lists Now, I want to create another sheet with the score criteria and score values f.e. column "number employees" // column "region" zero employees = score value "-10 // region north…
-
Nested IF statement with VLookup Formula
Hello, I have two sheets that contain data from employee responses to different questions. Employees answer at least one question per day. If an employee answers the question on sheet 1, I need that value, if they dont have a response on that sheet, I need to look and see if there is an answer is on a different sheet. I…
-
Estimate Date Planned against the target date
I need to calculate the planned date against the Go-Live date for each column. The dates are above the columns that should be prior to the Go-Live Target which is 10/31/22. I need to make the actual columns cells yellow for 3 dates against the planned date, red at the planned date. If a date is entered in actual column,…
-
Color Assignments in Circle Graphs
Hi, I need to add some pie charts/circle graphs to a dashboard. I want the order of the items in the legend to be High Risk, Medium Risk, Low Risk and No Risk. I want the colors Red, Orange, Yellow and Green. I setup the charts and all is well, but it changes the legend to alphabetical and it keeps reassigning the colors…
-
Count time between dates in Weeks/Days
How would I calculate the time between two dates (start and finish) in Weeks and Days "8weeks 4days"
-
Traffic light
Hi, I'm trying to set-up a traffic light formula with the following data fields: %Complete, Start Date and End Date. The parameters are: If TODAY is 5 days from End Date AND %Complete is <100% then Yellow If End Date is < TODAY, then Red Can anyone please let me know how the formula should be typed? I keep on getting…
-
Formula help = if date contains TBD, leave blank
Hello! I'm struggling with the 3rd part of my formula. I need to have the formula calculate: A date from the entered work week; be left blank if no work week is entered and leave blank if TBD is listed in the work week column 1 & 2 work using this formula: =IF(ISBLANK([Work Week]@row), "", (DATE(year@row, 1, 1) + 7 *…
-
Formula that updates Blank cells, ignores data when present
Hello everyone! Stumped trying to figure out a formula for our Pre Task Plan Smartsheet. We have a form submission process that users answer questions for their project. If they answer Yes, then they are asked to provide more information on 2 additional questions. However if they answer No, the other 2 questions are hidden…
-
Can you Index Match different sheets but have it check multiple rows also?
I am trying to Index Match 2 different sheets and it works when I only have one row that matches. I am trying to match the newest row in a series entry. Context: I have different quote numbers for different submissions on the master sheet form but I have another form on the SKU sheet that we use the same Quote number to…
-
Too many Cells referenced when I try to move rows into another sheet
Hi guys, I wanted to move rows into another sheet (around 100 rows). it is said that there are too many cells referenced by formulas in this sheet, which is odd, because the limit for cross-reference is upped to 250,000 last I checked. The total of Cross referenced column is 4, I currently have 912 rows in this sheet, and…