Excel nerds, to me!

[Tonitrus]

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?

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. 

[Liep]

=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.

[Tonitrus]

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).

[Tonitrus]

=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. 

[Syt]

Couldn't you also do a COUNTIF with INDIRECT row/column references, fed out of a MATCH for the name of the person?

[garbon]

On an excel front, I held a training for my employees that I called 'Pivot Table Palooza'. No wonder they hate me.

[Tonitrus]

Couldn't you also do a COUNTIF with INDIRECT row/column references, fed out of a MATCH for the name of the person?

Don't make Throg's brain hurt.  Fire bad. 

[DontSayBanana]

On an excel front, I held a training for my employees that I called 'Pivot Table Palooza'. No wonder they hate me.

I could spend an afternoon messing around in Pivot Table Palooza.

[garbon]