-
Formula- need to Sum OR Average based on another cell
I need some help with a formula. I have: =IF([Goal Calculation]@row = "sum", SUM([Quarter 1]@row:[Quarter 4]@row,IF([Goal Calculation]@row = "average", avg([Quarter 1]@row:[Quarter 4]@row)))) The problem is it is only updating to reflect SUM. If I change it to AVERAGE, the cell goes blank. So I switched it and put AVERAGE…
-
Count The Number of Upcoming Deliveries Within a Certain Number Days
Hey there! I've tried everything. All I am trying to do is produce a formula that counts the number of deliveries we have in a certain number of days that is not in "lost or cancelled" status. This helps us keep our eye on the prize to ensure we are doing all of our tasks prior to the delivery date so the customer can have…
-
Convert to Column Formula
Is there a work around for "Convert to Column Formula" in the Primary Column that has an hierarchy? We love the hierarchy groupings and I do have a formula there that gives a sneak peak of the name and site assigned at first glance but it is cumbersome to go to the sheet to copy the formula down. I was hoping it could be…
-
Employee Fit Testing Tracking
I am very new to creating my own smart sheets, I am looking to create a tracking of Employee Respirator Fit Testing. This sheet would need a away to annually updated by testing completion date as well as the type of mask they tested on, noting that some employees with have to complete fit testing on multiple masks each of…
-
Reference another sheet to return quantities from multiple rows
Hi all - I am building sheets with corresponding forms to allow individuals to receive and distribute supplies across several locations. What I need: a master inventory sheet reference other sheets, specifically for an item name and the quantities received. The quantities received will have multiple continuous entries…
-
Sum of hours from various cells and subsequent calc of FTE based on certain criteria
Hello, I would like to ask for help. I've got a sheet that can automatically calculate the FTEs of people assigned to projects but I need a little tweaking. There is a current layout: Currently, the FTEs are calculated based on what region is the global lead and that takes into account the "Lead MM - MDR Oversight Hours"…
-
Cell Link or Formula for Start and Finish Dates
I'm looking to find a solution to populate the start and finish date columns based on info in another sheet. However, because I have dependancies turned on the date columns cannot use a vlookup or other function to pull the dates from another sheet where they are stored. Here is the basic setup: Grid 1 includes dates…
-
Date column formula to start on a specific date and continue on after that.
Hello, So I have a helper sheet that tracks total number of entries for each month in each year. Right now I have a formula set up that checks the date above, and depending on if the current month is past that month, it will automatically fill in a new line with the current month (Due to date formatting, I have it set that…
-
Ranking Formula
Hello all, I have a sheet with columns that look like this: Spot in Queue Case Number Case Prep Completed Case Completed 1 100 x 2 101 x 3 102 Essentially, what I would like to do is change the spot in the queue when the case is completed. For example, when I check off 'Case Completed' for case number 100, the spot in the…
-
Calculation of 2 types of different hours in a single row
Hello all, So I've got this formula that automatically breaks down budgeted hours into individual cells based on Start and Finish Date and Total budgeted. Now I'd like to add another set to this formula that will calculate different hours and break them down too. The circled ones are already being calculated and the yellow…