-
Is there a formula for referencing a different cell daily?
I've automated emails to send out schedules to our field employees but their sites can change daily and I was wondering if there's a way to reference a different cell everyday so I don't have to manually change the site cell in the automated workflow.
-
Formula help: Check box if field in another sheet is a date
I am attempting to use an IF statement with an Index(Match( to check a box in my target sheet if a matching field in my reference sheet is a date. I am attempting to match the Vendor Names between the two sheets and have used the below formula but am getting a #NOMATCH error. =IF(ISDATE(INDEX({Contract Management - Sub…
-
Counting number of tasks per Assigned To contact
My task list contains parent, sub-parent, then children tasks. The workstream leader is assigned to both the parent and sub-parent tasks, and possibly sub-tasks as well. I need to count the number of tasks per "Assigned To" contact. It would be ideal if parent tasks are not included in that number, but it's manageable if…
-
Differentiate sections in master gantt chart with different colors to show different projects
I created a master gantt chart where the beginning and end dates are linked of different projects within one branch of the business. So each line would have columns like the below with the gantt chart to follow. Project 1 | 60 days | 3/1/21 | 6/1/21 Project 2 | 120 days | 3/1/21 | 9/1/21 Is it possible to show a particular…
-
formulas doesnt work when updating data from external source
I'm using Appsheet integration to extend the functionality in forms. I encountered a problem when trying to use the SUM formula - when a data is updated inside the columns from appsheet or another source (i only updating numbers) the formula in smartsheet doesn't recognise it and when trying to sum the numbers it shows as…
-
AVG(COLLECT)
I am attempting to average a column where the assignments are now closed (or past the due date) for the year. The Year@row is 2021. =AVG(COLLECT({Row Averages}, IFERROR(YEAR(@cell), 0) = Year@row, {QAlerts Closed}, 1)))
-
VLOOKUP resulting in some references coming back as #NO MATCH.
I have a master Smartsheet people fill-out a form for that submits purchase orders made. I have a corresponding Smartsheet that people fill-out a form for to indicate when a specific PO has been received. In the master Smartsheet I am attempting to use a VLOOKUP that looks at the PO number from the master Smartsheet and…
-
COUNTIFS Formula Issue
Hi all, I'm trying to count how many times a particular field appears in a range across two different sheets, but I keep getting an #UNPARSEABLE and I can't figure out why. My formula is below: =COUNTIFS({2021 Completed Sim Equipment}, "24 Week Airway Trainer",[{In Progress Sim Equipment}, "24 Week Airway Trainer"]) If I…
-
SUMIFS
Hi, I'm looking to sum the cells in a column if another corresponding column is either of two values. I'm getting #UNPARSEABLE though! 😕 =SUMIFS({Data Range Spend}, {Data Range Award Type}, OR(@cell = "Direct Award”, @cell = "Mini-Competition”)) Any thoughts welcome! Thanks, Derek
-
Summing FTE Hours available in the future
I am currently utilizing the current formula =SUMIF({Resource name}, [Resource Name]@row, {Weekly Scheduled Hours}) to determine how many hours a resource has available. Since I began using this, some assignments have ended, but resource utilization is still being summed, resulting in weekly hours scheduled being >40. How…