• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Horizontal VlookUp

ahmetk

Member
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.
 

Attachments

  • Renesola 2015 Visit Report.xlsx
    231.6 KB · Views: 7
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
 

Attachments

  • Renesola 2015 Visit Report_PaulF.xlsx
    219.3 KB · Views: 4
Last edited:
Dear Paul,

Works perfect, exactly what I needed :)

Thank you very much for your kind and prompt help.

Kind regards.

Ahmet K.
 
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
 
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
 
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(),)
 

Attachments

  • Renesola 2015 Visit Report LM.xlsx
    219.6 KB · Views: 6
Back
Top