Excel Challenge - Assist Bringing Totals From One Sheet To Another Using Validation List

Hello,

Hoping someone can assist.  Im looking for either a formula or code that will do the following:
 Once a position is selected in Developers tab cell B3, a formula or code will sum the totals for various stages from the Open Position Data tab and place those totals in the appropriate cells on the Developer tab (See Example File).

For Example:
If Quaterback is in cell B3 (Developers tab), if would show 1 in cell H13 because there is only one row listed in the Open Position Data tab showing a Quarterback who has a status of New- To Be Evaluated and the applicant is external.  The code or formula will need to take into consideration if the position is internal or external by looking at row F of the Open Position Data tab.

A second example would be the position of Waterboy which would show a 2 in cell K19 because there are two applicants, listed as waterboy, with a status of Pre-Screen - Left a Message, that are both External.

Any assistance would be greatly appreciated!!!!!
EE-Sample.xlsx
LVL 1
EscanabaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
You need to make sure that the descriptions in worksheet Open Position Data column C match those in worksheet Developers rows 11 and 17. Assuming that this is the case, you can use formulas like:
=SUMPRODUCT(('Open Position Data'!$B$1:$B$1000=$B$3)*('Open Position Data'!$C$1:$C$1000=H$11)*('Open Position Data'!$F$1:$F$1000="Yes"),'Open Position Data'!$D$1:$D$1000)

It is worth noting that the descriptions in Developers row 17 omitted "Pre-Screen - ", but that can be prefixed in the formula:
=SUMPRODUCT(('Open Position Data'!$B$1:$B$1000=$B$3)*('Open Position Data'!$C$1:$C$1000="Pre-Screen - " & K$17)*('Open Position Data'!$F$1:$F$1000="Yes"),'Open Position Data'!$D$1:$D$1000)

The sample workbook attached shows these formulas where I saw matching description fields. Where a description was present on Developers, but missing from Open Position Data, I omitted the formula.

I started off using the entire column in the SUMPRODUCT, but there was a delay of a second or so for each formula to update. Such a delay would be intolerable in a production situation, so I restricted the formula to rows 1 through 1000. The formula should extend beyond any anticipated amount of data, but stop well short of the million plus rows that are possible in a .xlsx workbook.
EE-SampleQ28266705.xlsx
0
EscanabaAuthor Commented:
Greatly appreciated.  If I wanted to get a sum of multiple positions at once how would I adjust the formula or is that even possible?  For example, instead of a drop down list there are 3 positions listed starting at cell B3 to B6 (Quarterback, Receiver and Waterboy).  Is it possible for SUMPRODUCT to work on multiple cells?

I dont want to take advantage so if you feel this needs to be a separate question please let me know and I can make another one.
0
EscanabaAuthor Commented:
Or the other option would be to add "All" to the validation list and have the formula pull totals for all the positions.
0
byundtMechanical EngineerCommented:
To handle 3 jobs at once, use a formula like this:
=SUMPRODUCT((('Open Position Data'!$B$1:$B$1000=$B$3)+('Open Position Data'!$B$1:$B$1000=$B$4)+('Open Position Data'!$B$1:$B$1000=$B$5))*('Open Position Data'!$C$1:$C$1000=K$11)*('Open Position Data'!$F$1:$F$1000="No"),'Open Position Data'!$D$1:$D$1000)

To get All positions, just remove the test for column B:
=SUMPRODUCT(('Open Position Data'!$C$1:$C$1000=K$11)*('Open Position Data'!$F$1:$F$1000="No"),'Open Position Data'!$D$1:$D$1000)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EscanabaAuthor Commented:
Perfect!  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.