3D References / Running Tally for an entire workbook

Rikku_

New Member
Joined
May 8, 2019
Messages
1
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 1) EE Names(Table 2)
SamLate
MattAbsent
Cindy
Jen
Terry

<tbody>
</tbody>

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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Forum statistics

Threads
1,136,279
Messages
5,674,821
Members
419,529
Latest member
TommasoP11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top