Horizontal VlookUp

[ahmetk]

Dear Excel Gurus,

I have an excel file (please see the attached file) that I have prepared to follow up my sales team's country wide visits.

Each sales rep enters the total number of customer visits they do at a certain city at a certain date.

My problem: I can't find an easy way to bring the total figure for each city for each month under the summary page.

Example: Sales rep A has visited 2 customer in Adana on 26.01.2015 and 3 customer on 27.01.2015. Total customer visits for Adana for Jan 2015 is 5. On the "visit summary" page I would like to see 5 next to Adana under Jan 15. And that should repeat for all cities for all months.

Is there an easy way to do that?

Thank you and kind regards.

Ahmet K.

 

[PaulF]

Ahmet,

I changed the CF on your visits page to be automated:

• =WEEKDAY($A2,2)>5 (Weekend - Fill Light Grey)
• =LEN($A2)=3 (I changed your Summary Labels to Jan, Feb, etc... Fill Yellow and Border Top/Bottom)

On the Visit Summary sheet I changed the Dates on the top to Jan, Feb, etc...

• To get the data over I used =Index Match/Match
• =INDEX(Visits!$B$2:$CD$378,MATCH(C$1,Visits!$A$2:$A$378,0),MATCH($A2,Visits!$B$1:$CD$1,0)) (Drag Across then Down)

Let me know how this works for you...

Respectfully,
PaulF

 

[ahmetk]

Dear Paul,

Works perfect, exactly what I needed

Thank you very much for your kind and prompt help.

Kind regards.

Ahmet K.

 

[Hui]

In Visit Summary C2:
=SUMPRODUCT(Visits!$B$2:$CD$378,(Visits!$A$2:$A$378>=C$1)*(Visits!$A$2:$A$378<=EOMONTH(C$1,0))*(Visits!$B$1:$CD$1=$A2))/2
Copy across and down

 

[ahmetk]

Dear Hui,

Thank you for your kind reply as well.

Kind regards.

Ahmet K.

 

[Hui]

Having the Monthly Totals in the table with a valid date as a Header causes things to be counted twice
Generally try and limit subtotalling in Tables for this reason, you can always add another report later if that is what you require

 

[Luke M]

Here's my attempt, uses the TEXT and VLOOKUP function.
Formula in C2 of summary:
=VLOOKUP(TEXT(C$1,"mmm yy"),Visits!$A:$CD,ROW(),)