Data blending - Retreving data from rows in seperate pages, based on IDs and names

[Philbert1]

Hi,

I’m working for a small company, and have been using excel daily for a couple of years. We want our reports to become more automated, and stop using time punching in hours and results to calculate our daily earnings. I have two separate data sources that I use in our daily production. One calculates how many hours we use per project and the other shows the result per project. I use excel to blend these numbers to show statistics on campaigns, agents and revenue. In order to take my report further, I want to be able to copy/paste in the data from our data sources, and have the excel-sheet automatically gather the relevant data and summarize it per campaign and agent. I have tried playing around with vlookup and IF formulas, but I can’t seem to crack the code.

I have attached a very simplified data set, that shows test numbers for both hours per campaign and results per campaign. The clue is to find a formula or function that would calculate the hours for the two different projects from the first data source, and calculate the results from the second data source.

I am very grateful for any contributions. Please let me know if any further explanation is needed.

- Philbert

[JeteMc]

Welcome to Excel Forum.
For Hours try the formula:

Please Login or Register  to view this content.

For Result try the formula:

Please Login or Register  to view this content.

Note: the formula for Data Source 1 column E has been changed to =D6-C6
Let me know if you have any questions.

[dflak]

First of all, I had to correct your data source 2. You apparently dragged down N92 so the subsequent cells had N93, N94, etc. I copied down and made them all N92 .

I also changed the data into Excel Tables. It’s easier to work with column header names than it is column letters. Also tables know how big they are so Table_Data1[Campaign] refers to exactly the number of rows in Table_Data1 and I don’t have to change any formulas when the number of rows change.

Here’s some information on tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

Other than that, it’s strictly formulas:

You can look up the campaign name from data source 2: =INDEX(Table_Data2,MATCH(B31,Table_Data2[Campaign NR],0),1)

The other two formulas are SUMIFS:
=SUMIFS(Table_Data1[Hours],Table_Data1[Campaign],B31)

=SUMIFS(Table_Data2[CALLS],Table_Data2[Campaign NR],B31,Table_Data2[DISPOSITION],"Regret") +SUMIFS(Table_Data2[CALLS],Table_Data2[Campaign NR],B31,Table_Data2[DISPOSITION],"Call Completed")

[Philbert1]

Thank you so much! The excel sheet turned out great. Im saving countless of hours each week.

Again, thank you!

Philbert

[Philbert1]

Thank you so much! The excel sheet turned out great. Im saving countless of hours each week.

Again, thank you!

Philbert

[JeteMc]

Thank you for the feedback. As it appears that the solutions satisfy the query, please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.