Automated report from data on 3 sheets

[LHRIXGU]

Dear All,

I have to generate an automated report combining data from 3 worksheets.

Sheet 1:
Agent names and addresses (just 8 lines)

Sheet 2:
Locations assigned to each agents (31 lines)

Sheet 3:
Revenue data by location/month from beginning 2009 to end 2010 (6000+ lines)


I have to fill the report on page 4 with revenue growth comparisons and a graph showing the month-by-month PY comparison.
There is a drop-down agent selection field, and the idea is that you select Agent 1, you have name and address automatically filled, as well as the revenue data and graph updated.

I have the picture clear in my mind, but I have never used this kind of functions, our Excel needs are mostly focused on Pivot tables.

Would it be useful to create a pivot table out of each sheet?

Thanks!

 

[LHRIXGU]

Ooops, I posted the same thread twice, sorry

 

[Peter_SSs]

Ooops, I posted the same thread twice, sorry

OK, I've closed the other thread.

 

[LHRIXGU]

Ok thanks, now I hope to get some reply... I managed to extract the address data with a VLOOKUP function, but I am not so good at other functions, I hope I don't sound too much like a newbie, which I am not )

Should I post files maybe?

 

[Gerald Higgins]

Maybe some kind of SUMIF or SUMPRODUCT formula, but it's difficult to be precise because your request is a bit vague.

 

[LHRIXGU]

I have uploaded the file here, care to take a look?