Lookup, Match, and Sum Data from Multiple Workbooks

[Capt Call]

Greeting Professionals,

This will be my first of many posts on this forum. I have searched and used many of your solutions over the past few weeks and they have all been helpful!
The preface here is that I am a novice with formulas and am just now getting my feet wet.

Scenario:
In one workbook I have a drop-down list of clients residing in Cell CV4. I also have the selected client name copied the cell below CV5.
In Column CU (beginning at cell CU7) I have a list of Months.
I also have reports from individual dispatchers in separate Workbooks (each dispatcher report is formatted identically).
In the dispatcher report I track sales by client by month.

Task:
I need to enable the first Workbook to sum total sales by client across all dispatcher reports according to the selected client name from the drop-down selection.

I tried to include attachments of the Workbooks, but am not allowed to attach files.

I would like to formally thank any and all of you in advance for any help you can provide!

 

[Chrisdontm]

In order to reference other reports/pages/tabs/worksheets, you need to have the name of the sheet.
All the info you want extracted would need to be within the formulas, you just need to be more specific.

Is that list of Months, just Jan.-Dec. or does it include the years along with it...?

It sounds like a link would work good...

If you go back to the forum pages, and click on the top choice mentioning add-ins, it gives you a variety of things to do...

http://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html

 

[Capt Call]

Thank You Chrisdontm for your help and suggestions. Below, I will try to provide more explanation.

I am working in the below file. In cells CV4, CW4, and CX4 I have created a drop-down list of Client names.
The chosen name from each drop-down selection is copied into CV5, CW5, and CX5 respectively.
In Column CU (beginning at CU7) I have a list of months.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]CU[/TD]
[TD="align: center"]CV[/TD]
[TD="align: center"]CW[/TD]
[TD="align: center"]CX[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD="align: center"]"Drop-Down Client List"[/TD]
[TD="align: center"]"Drop-Down Client List"[/TD]
[TD="align: center"]"Drop-Down Client List"[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD="align: center"]"Copied Selection"[/TD]
[TD="align: center"]"Copied Selection"[/TD]
[TD="align: center"]"Copied Selection"[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]11/30/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]12/31/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]1/31/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]2/29/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]3/31/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]4/30/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]5/31/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My goal is to find the results from other Worbooks. For each of my employees, I have a workbooks that track their individual sales by client.
These Data Source Workbooks consist of identical layouts, but Row 8 in each will have different series of "Client Name"

Data Source Layout Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]AA[/TD]
[TD="align: center"]AB[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]"Client Name"[/TD]
[TD="align: center"]"Client Name"[/TD]
[TD="align: center"]"Client Name"[/TD]
[TD="align: center"]"Client Name"[/TD]
[TD="align: center"]"Client Name"[/TD]
[TD="align: center"]"Client Name"
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]11/30/2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]12/31/2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]1/31/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]2/29/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]3/31/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]4/30/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]97[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]5/31/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]6/30/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]


Task: When specific "Client Name" is selected in on of the Drop-down lists in first Workbook (cell CV4, CX4, and/or CW4), I need to query all of the Data Source Workbooks for sales by "Client Name" and populate the sum in the corresponding cells according to date (beginning with CV7, CX7, and/or CX7 respectively).

I hope this helps with the clarity.
Thank you all in advance for your help on this.

 

[Chrisdontm]

Two questions;
Why do you copy the drop down, one line below the drop down?
and,
When you say "Source Workbook", you are referring to a different document, not just another sheet, or tab, within your workbook, right....?
In order to reference another workbook or worksheet, we would need to know the name of the workbook/worksheet...
Also, when referencing another workbook, it has to be open for the formula to update.

If that is the case, you could have a different worksheet within your Main workbook for each employee, and link their records to your sheet.
Then on your main page have your formulas refer to the tab within your workbook...

 

[Capt Call]

Chrisdontm,

Thank You again for the help.

Question 1:
Why do you copy the drop down, one line below the drop down?

There is no particular reason that I do this other than to have a cell with a result instead of a dynamic selection. I thought this would make it easier to format a formula.I can remove this if necessary.

Question 2:
When you say "Source Workbook", you are referring to a different document, not just another sheet, or tab, within your workbook, right....?

The "Source Workbooks" are Excel Workbooks that I am using. I have one Workbook for each dispatcher. In other areas of my "Results Workbook", I reference these exact "Source Workbooks" without any problem and they don't have to be open for the formulas to update correctly.
Here is an example of an external reference formula I have been using with success.
='C:\Users\Deron\Dropbox\HCC Reports\Corp. Relo. Delivered Totals\Robert Powell\[Robert P._C.R.Delivered Running Total.xlsx]Sheet1'!R9

I'm just having trouble figuring out what type of formula and math I need to accomplish the result.

Thanks again for your patience and help.

 

[Chrisdontm]

Capt Call,

As for the line below the drop down, it doesn't matter whether you keep it or not. Actually once you reference it to the drop down, then it becomes dynamic as well...

Also, the reason I said the Workbook has to be open is because the formula I was using to try and make it work is the INDIRECT formula.
It looks at a text and uses it as part of a the creation of a reference/formula.
When using it, it doesn't seem to work if the other workbook is closed.

But true, if you have a straight forward formula linking to another workbook it doesn't have to be open.
So is there a way to have a tab within your main workbook linked to each workbook, and then you can link to those tabs rather easily...
That's all I can think of to get you there. ( Sorry if this was all a disappointment )

I too am open for any suggestions...