-
VLOOKUP Reference Vacation Calendar to Update Approver via Automated Workflow
Hi, I have an employee Vacation Planner sheet with a status column/formula that indicates whether an individual is currently on "PTO" based on today's date and whether it falls in the range of their vacation's start and end date. In a separate Smartsheet, we will be managing service tickets and certain types of requests…
-
COUNTIFS multiple columns
Hi @Paul Newcome I have another interesting scenario I wonder if I can solve with a formula. I have three columns with numbers 1....5 I want to calculate between those three columns anytime a value is >=1 to count as 1 and not every time the value is true. I know I can count the three columns =COUNTIFS([Summer…
-
Formulas and Links to Other Sheets Disappeared
I have a large project sheet that contains (1) cross-sheet links and (2) formulas to calculate due dates based on other tasks. I needed to change the sheet to only populate weekdays (no weekends) so I turned on the dependencies and edited the workdays. Then I realized ALL of my cross-sheet links and formulas disappeared! I…
-
How to calculate total hours of work per month for each resource
Hi all, l am trying to sum the total number of hours each person within our team has worked across different contracts. However l am not sure the correct formula to use for this? I believe if would be a SUMIF but every formula l attempt either brings it back as invalid or unparseable. I have attached a screenshot the…
-
Status RGYB balls formula, when due tomorrow and past due
I built out this formula to change the status marker balls colors based on today's date and my "due Date" column: =IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow",…
-
Column Formula to mimic HLOOKUP?
Hi, Gurus! I have a sheet that calculates 2 values based on whether the calculated columns contain an "X" in Row 2: #Actual formula is: =SUMIF([Week 1]$2:[Week 300]$2, "X", [Week 1]@row:[Week 300]@row) [has "X"] #Forecast formula is: =SUMIF($[Week 1]$2:$[Week 300]$2, "", $[Week 1]@row:$[Week 300]@row) [no "X"] I need to…
-
Adding Matching Barcodes/SKU's from a transfer sheet onto a master Inventory sheet
Hello all, I'm new to smartsheets and working on setting up a barcode based inventory management system for the warehouse I run. I feel like I have the transfer sheet up and running just need to get all of that information to condense onto my sheet. Would someone be able to point me in the right direction or find what I'm…
-
SUMIFS #INCORRECT ARGUMENT SET
Can some please explain the reason for the error? I don't understand what is wrong with what I have typed. =SUMIFS({STD HOURS}, {STATUS}, ="*** COMPLETE ***", {COMPLETION DATE}, AND(IFERROR(WEEKNUMBER(@cell), 0) = [Week #]@row, IFERROR(YEAR(@cell), 0) = {YEAR SELECTED})) {STD HOURS} is a column with a formula that returns…
-
Auto drag down TODAY formula
Hello! I have a sheet with a column designated to always show the =TODAY(0) formula. However, I noticed that when new rows are added, I have to manually drag down the formula. Is there a way to auto-populate the column and lock it so each new row will always have this formula? Thanks!
-
Format cells in sheets and reports that have an update request
Has anybody found a way to highlight (change cell color or format) a cell or row in a sheet, once an update request has been manually requested by a user? Additionally how can the same highlight be extended to reports built off of that sheet? We have users manually requesting updates to a single column in a report, but as…