-
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…
-
ISBLANK Formula
Is there a limit to how many nested IF statements you can have in one formula? I have six columns and trying to pull data for dashboards based on the content in each column. Only one column will be populated with data depending on the project plan type. But I cant seem to get this formula right? Can anyone see my error??…
-
How can I search another sheet for a name match within a certain date range?
I'm struggling to create a formula that will not only look for a name match from one sheet to another, but it will only return a match if the date on the row was within the past 6 months. For finding a name match between sheets, I've been utilizing the following formula: =IF(CONTAINS(Contractor@row, {Change and Offboarding…
-
VLOOKUP to Return Two Users
Hi! I need a formula that looks at two columns and returns an email address based on vlookup if one of the two columns is blank and two email addresses (again based on vlookup) if neither column is blank. The formula works well when one of the columns is blank and only one email address is returned. The problem is when two…