Cell Linking or Retaining Daily Counts

Christina Eldergod · 05/07/20

I have a big question on Smartsheet capabilities. I am looking to understand if there is any option or way to stop cells from linking. I have an attendance sheet that feeds into a sheet that calculates totals (daily, weekly, monthly, etc.) I need a way to release the cell links once the day is complete. Is there any way you see it being possible to program this?

Each employee has a row and within their row is a status dropdown, these are what are being totaled. How many "On Time", "Late", etc.

So for Drew’s crew Monday had a total of 2 people who were "On Time". I have the CountIf function giving me a total of 2 Drew's Crew for "On Time" on Monday. But on Tuesday when the first sheet is changed to reflect his crew went up to 5 on the totals for Monday are also changing. Please let me know if there is any way to automate the cell to drop the link. Or if there is another way to have the data total that day, then not change when crews are reassigned.

My formula is: =COUNTIFS({Range 1}, "On Time", {Range 2}, "May, Week 1", {Range 3}, "Drew")

Crews get reassigned daily so I don't know how to log this information on an ongoing basis.

Thank you in advance,

Mary_A · 05/08/20

@Christina Eldergod You need to manually delete a cell link.

It seems to me, the problem you are working begins with ensuring you are capturing the attendance data in a way that makes it easy to report. You want to find out how many members of the crew were On Time each day. You could build an Attendance sheet that has these columns

Week Start Date, Employee Name, Foreman, Date, ClockedIn

ClockedIn in this case is the equivalent to your Reason. ClockedIn is a bit more evocative as this seems to be what you are capturing -- did this employee clock in on time or was it some other value. This sheet structure can keep a running log of each employee's daily attendance. Employees can use a simple form to report for themselves. (I could not tell from your description how you were collecting the data on your current sheet.)

From this Attendance sheet, you can build a number of reports easily. You could also, create a pivot sheet that calculates ClockedIn = On Time (or Absent whatever) from your Attendance sheet by Foreman. You would use the WEEKDAY(date) formula to convert the date entered into a day of the week.

By the way, if you wanted to simply record when an employee clocks in and then based on some known time value determine if they are On Time, you could do that as well with a form.

Christina Eldergod · 05/08/20

Thank you, I think I may have misled with my example since I mentioned "On Time" instead of "At Work". The issue I am facing rather than "On Time" is an attendance reason record so my actual statuses are "At Work", "Sick", "Personal", "School".

I am trying to track the daily result for each Crew. But as Crew's may shift daily, I am having a hard time creating a record.

Carolina Rivera · 06/05/20