Dave, Gordan, someone help!

[Guest]

Sheet 1
Index Doctor Month Year Date Production
1 Whitney Jan 2004 1/31/2004 21000
2 Whitney Feb 2004 2/28/2004 22000
3 Chong Jan 2004 1/31/2004 18000
4 Whitney Mar 2004 3/30/2004 20000

Sheet 2
With a drop down box I would like to be able to choose the (year and doctor)
which would fill in the correct production info in column b
A B
Month Production
Jan xxxxx
Feb xxxxx
Mar etc.
Apr
May
Hope this is plenty of info. I know how to make it work if it just had to
match one variable but since I want to match two things I'm stuck.

 

[Biff]

Hi!

What exactly do you want to return?

The SUM of production for a cetain doctor for each month
of a certain year? If so, then you have 3 conditions.

Not difficult, just need clarification.

Biff

 

[Guest]

Hi Biff,
Thank you for your time. In the drop down boxes I want to choose for example:
Sheet 2
Year:2004 Doctor: Whitney
and it look at sheet 1 and find the correct production $$ for each month
available on Sheet 1. So . . . sheet two would look something like:
Year: 2004 Doctor: Whitney
A B
Month Production
Jan 21000 (formula fills this number in)
Feb 22000
March 20000
April N/A

Thank you so much. Jennifer

 

[Biff]

Hi!

Sheet1 col B = Doctors
Sheet1 Col D = Year
Sheet1 col F = Production

Sheet2 A2 = Year dropdown
Sheet2 B2 = Doctor dropdown
Sheet2 A5:A16 = months: Jan,Feb,Mar...Dec

Formula in Sheet2 B5:

=SUMPRODUCT(--(Sheet1!D$2$5=A$2),--(Sheet1!B$2:B$5=B$2),-
-(Sheet1!C$2:C$5=A5),Sheet1!F$2:F$5)

Copy down as needed. Where there is no matching data a 0
(zero) will be returned.

Biff

 

[Biff]

Ooops!

I forgot about the months.

Sheet1 col B = Doctors
Sheet1 Col D = Year
Sheet1 col F = Production

Should include:

Sheet1 col C = Months

Biff

 

[Guest]

Your awesome. Thank you. I will try to further understand the formula
tomorrow but for now it works and I will sleep much better. Thank again.
Jennifer

 

[Biff]

You're welcome and thanks for the feedback.

Biff

-----Original Message-----
Your awesome. Thank you. I will try to further understand the formula
tomorrow but for now it works and I will sleep much better. Thank again.
Jennifer


.