Hello all. First time poster.
I'm trying to create a way to track "Late" and "Absent" occurrences in an Excel workbook that I have already created. These workbooks are for every month, and in each one is a sheet for every day including an extra sheet that shows an overview of the entire month. So about 30 to 32 sheets depending on the month. They are used for daily notes and tracking sales, labor, and other data. It's a small operation and I'm only providing a quick summary of what I already have.
Here's what I have so far for the current question:
(Generic info of course)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD](Table 1) EE Names[/TD]
[TD][/TD]
[TD](Table 2)[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD][/TD]
[TD]Late[/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD][/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]Cindy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jen[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I created 2 tables and turned them into drop down lists in specific cells on the Overview page. I inserted the same drop down lists into 3 cells each. So if Terry was late I could scroll to "Terry" in one cell and mark him as "Late" in the cell directly next to his name in the same row.
My original plan was to have it so there was a running tally for each month that would auto-populate on the overview sheet. So if the table of names was in A1 on the overview sheet, "Late" would be in B1 and "Absent" would be in C1. Then use the COUNTIFS function to have it add to the proper column and corresponding row next to that name each time the event of a name was selected and was listed as one or the other.
So this is when I learned the COUNTIFS function cannot be used for 3D References. So this works on "DAY1" but will not let me add in other sheets even if the cell information is identical across all sheets.
I'm a fairly basic Excel user in the grand scheme of things. I have spent a decent amount of time with it, but I've just recently started to try and really get to know more about it. That said, I'm not looking for an easy answer. Even if someone can point me in the starting direction of where to look and learn on my own I'd be very grateful.
Also, any good references for books that maybe focus on learning the formulas more would be fantastic as well.
Thanks in advance for your time! I'll be checking back frequently to reply if you have any questions. Hopefully I explained it well enough to make sense.
Thank you,
- Me
I'm trying to create a way to track "Late" and "Absent" occurrences in an Excel workbook that I have already created. These workbooks are for every month, and in each one is a sheet for every day including an extra sheet that shows an overview of the entire month. So about 30 to 32 sheets depending on the month. They are used for daily notes and tracking sales, labor, and other data. It's a small operation and I'm only providing a quick summary of what I already have.
Here's what I have so far for the current question:
(Generic info of course)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD](Table 1) EE Names[/TD]
[TD][/TD]
[TD](Table 2)[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD][/TD]
[TD]Late[/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD][/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]Cindy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jen[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I created 2 tables and turned them into drop down lists in specific cells on the Overview page. I inserted the same drop down lists into 3 cells each. So if Terry was late I could scroll to "Terry" in one cell and mark him as "Late" in the cell directly next to his name in the same row.
My original plan was to have it so there was a running tally for each month that would auto-populate on the overview sheet. So if the table of names was in A1 on the overview sheet, "Late" would be in B1 and "Absent" would be in C1. Then use the COUNTIFS function to have it add to the proper column and corresponding row next to that name each time the event of a name was selected and was listed as one or the other.
So this is when I learned the COUNTIFS function cannot be used for 3D References. So this works on "DAY1" but will not let me add in other sheets even if the cell information is identical across all sheets.
I'm a fairly basic Excel user in the grand scheme of things. I have spent a decent amount of time with it, but I've just recently started to try and really get to know more about it. That said, I'm not looking for an easy answer. Even if someone can point me in the starting direction of where to look and learn on my own I'd be very grateful.
Also, any good references for books that maybe focus on learning the formulas more would be fantastic as well.
Thanks in advance for your time! I'll be checking back frequently to reply if you have any questions. Hopefully I explained it well enough to make sense.
Thank you,
- Me
