Background: I run/operate my workplace/shift schedule, and have it organized on an Excel spreadsheet that I am slowly trying to improve. However, I have one function goal that has become my White Whale...
The basic setup is that one sheet is a master personnel roster (which has all the needed date/profile of each person), and then I have separate sheets for each month. Those monthly sheets are organized with a column of the personnel on the left side (vertically), several columns to the right of that (with data that fills in automatically from the master sheet), and then what is basically a giant square grid/table (but it's not set up as a table) that shows the days of the month. Overall it looks something like this:
| | January
-------------------------------------------------------------------------------------
Name | Headers of stuff | 1 2 3 4 5 6 7 8 (and out to 31)
------------------------------------------------------------------------
Smith, Bubba | stuff stuff stuff stuff stuff | W W - - W W - - W W (W is work days, - might be days off)
Flacco, Joe | stuff stuff stuff stuff stuff | - - W W - - W W - -
Also, I have it set up so that names can also be selected from a drop-down (using Data Validation) from the "master" sheet, which then fills in the stuff. Alterations to the work days (leave, CTOs (compensated time off, i.e. hit days), etc) have to be hand-jammed in. But what I want to do, is that whenever I set a specific day for a CTO, is to have a formula on the "master" sheet that will check the "Name" column for a specific name and then match up and then count in the calendar grid where "CTO" appears in the same row. Ultimately, this formula would combine the values from every subsequent monthly sheet, and display on the "master" the number of CTO days used (the shift guys get 16 CTO days over the year for having to work holidays/12-hour days) for the entire year.
I've been scouring google/youtube, and it seems like =COUNTIF or =SUMPRODUCT might be the formulas I want to look at, but I am not finding any good examples/tutorials that match what I am trying to do.
Any ideas? :hmm:
Note: Assume I am mostly an Excel idiot, as I've been teaching myself from zero knowledge over the last few weeks. :P
So you want to count how many times CTO appears across multiple sheets for a specific name?
=countif(Sheet1!a1:Sheet1!aa1,"CTO")+countif(Sheet2!a1:Sheet2!aa1,"CTO")... would do it, no? If you keep the names on the same rows across all sheets that is.
Or maybe =SUMPRODUCT((Sheet1!A1:A30="Name")*(Sheet1!B1:AB30="CTO")) is easier actually...
Quote from: Liep on July 27, 2016, 05:55:08 AM
So you want to count how many times CTO appears across multiple sheets for a specific name?
=countif(Sheet1!a1:Sheet1!aa1,"CTO")+countif(Sheet2!a1:Sheet2!aa1,"CTO")... would do it, no? If you keep the names on the same rows across all sheets that is.
Yeah, the trick is that the row might change between sheets.
Quote from: Liep on July 27, 2016, 06:17:29 AM
Or maybe =SUMPRODUCT((Sheet1!A1:A30="Name")*(Sheet1!B1:AB30="CTO")) is easier actually...
A lot of what I had found last night had me looking at the SUMPRODUCT, but I haven't tried it yet. I will today and report back. :)
Quote from: Tonitrus on July 27, 2016, 09:22:35 AM
Quote from: Liep on July 27, 2016, 06:17:29 AM
Or maybe =SUMPRODUCT((Sheet1!A1:A30="Name")*(Sheet1!B1:AB30="CTO")) is easier actually...
A lot of what I had found last night had me looking at the SUMPRODUCT, but I haven't tried it yet. I will today and report back. :)
I think that might be it. I have a similar sheet but working days are in rows. I use countifs to set multiple criteria to calculate vacation time spent, but I can't get that to work for multiple columns.
Yeah, didn't work out. Cannot even get it for one column. The closest I got is a value error, but SUMPRODUCT seems to say "nope" at even using a different sheet.
I'm using google spreadsheet but it's usually pretty similar to excel, and it's working fine on different sheets here.
Vba?
I got it to work!
Formula ended up looking like:
=SUMPRODUCT(--(January!$B$4:$B$122="smith, joe")*(--(January!$S$4:$AZ$122)+(SUMPRODUCT(--(February.... (And it repeats from there for each month).
It seemed the "--" is what was needed to make it work.
I tried a trick I found on one sight with the idea of Defining a Name to combine the monthly sheets, in order to simplify the formula, but that didn't seem to work. Even the above formula, while still displaying the desired number, still indicates having some kind unknown error.
The lame part is having to alter the formula for each of the 90-ish personnel on the master sheet...but that just takes copy/pasting the formula into a Word doc and doing "find/replace".
Can you replace "smith, joe" with "B4"? and then just pull it down?
Quote from: Liep on July 29, 2016, 04:19:56 AM
Can you replace "smith, joe" with "B4"? and then just pull it down?
Like I said, the names might not stay on the same row month-to-month, otherwise I would.
Quote from: Tonitrus on July 29, 2016, 08:54:12 AM
Quote from: Liep on July 29, 2016, 04:19:56 AM
Can you replace "smith, joe" with "B4"? and then just pull it down?
Like I said, the names might not stay on the same row month-to-month, otherwise I would.
Sure, but I mean only on the mastersheet
Quote from: Liep on July 29, 2016, 12:28:17 PM
Quote from: Tonitrus on July 29, 2016, 08:54:12 AM
Quote from: Liep on July 29, 2016, 04:19:56 AM
Can you replace "smith, joe" with "B4"? and then just pull it down?
Like I said, the names might not stay on the same row month-to-month, otherwise I would.
Sure, but I mean only on the mastersheet
Wouldn't it have to compare/match the names/CTOs on the "monthly' sheets in order to work? Or am I overlooking something simple? :hmm:
Tonitrus = freaking genius
Quote from: Tonitrus on July 29, 2016, 10:29:20 PM
Quote from: Liep on July 29, 2016, 12:28:17 PM
Quote from: Tonitrus on July 29, 2016, 08:54:12 AM
Quote from: Liep on July 29, 2016, 04:19:56 AM
Can you replace "smith, joe" with "B4"? and then just pull it down?
Like I said, the names might not stay on the same row month-to-month, otherwise I would.
Sure, but I mean only on the mastersheet
Wouldn't it have to compare/match the names/CTOs on the "monthly' sheets in order to work? Or am I overlooking something simple? :hmm:
I think I see what you mean...because the formula is on the master sheet.
The problem, I think, is that the formula refers to all of the monthly sheets, and compares the name on those sheets to the CTOs. I don't think (and I may be completely wrong), that I could do that drag-down on the master, and have it automatically change all of the names to match those names listed on the master.
But as I said, I am an Excel rookie, so I'd readily own up to overlooking something stupid. :P
=SUMPRODUCT(--(January!$B$4:$B$122=A1)*(--(January!$S$4:$AZ$122)+(SUMPRODUCT(--(February....
Assuming that A1 is smith, joe on the mastersheet, you already have the locking $ signs in there, so the only thing that should change when you drag it is A1. And where ever smith, joe is on the following sheet shouldn't matter because it's only A1 on the master that counts, it'll still look through january B4-B122 for the name.
Maybe I am missing something, but it wouldn't be able to check for the match between the name on the master sheet (instead of the name on the monthly sheet) and the CTO cells on the monthly sheet would it? While the names on the monthly sheets are populated by a drop-down that draws from the master sheet, wouldn't it still need that match to occur on the monthly sheet?
I may just be a dumbass, but I cannot see how the drag/copy would work in this case (though I would love it if it did). :hmm:
Quote from: Liep on July 30, 2016, 02:36:19 AM
=SUMPRODUCT(--(January!$B$4:$B$122=A1)*(--(January!$S$4:$AZ$122)+(SUMPRODUCT(--(February....
Assuming that A1 is smith, joe on the mastersheet, you already have the locking $ signs in there, so the only thing that should change when you drag it is A1. And where ever smith, joe is on the following sheet shouldn't matter because it's only A1 on the master that counts, it'll still look through january B4-B122 for the name.
As I said above, I didn't think that would work...not across all of the other sheets...but I tried it, and it did!
Thanks for the help Liep. :)
Couldn't you also do a COUNTIF with INDIRECT row/column references, fed out of a MATCH for the name of the person? :nerd:
On an excel front, I held a training for my employees that I called 'Pivot Table Palooza'. No wonder they hate me. :blush:
Quote from: Syt on September 09, 2016, 02:39:44 AM
Couldn't you also do a COUNTIF with INDIRECT row/column references, fed out of a MATCH for the name of the person? :nerd:
Don't make Throg's brain hurt. Fire bad. :mad:
Quote from: garbon on September 09, 2016, 02:48:48 AM
On an excel front, I held a training for my employees that I called 'Pivot Table Palooza'. No wonder they hate me. :blush:
:wub: I could spend an afternoon messing around in Pivot Table Palooza.
:D