+ Reply to Thread
Results 1 to 6 of 6

Best way to organize a pipeline?

  1. #1
    Registered User
    Join Date
    05-01-2014
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    23

    Question Best way to organize a pipeline?

    Hi all, I work in sales and I use excel 2010 to keep track of my sales pipeline (what deals I am currently working on). I'll set the situation up so you can actually understand the problem and maybe impart some wisdom on me.

    The situation
    I have 2 sheets in my workbook. Sheet1 is dynamic, and called Pipeline, and Sheet2 is static and called ClientInfo. The Pipeline sheet changes multiple times a day with current deals that I am working on for my clients, and ClientInfo has the static account information in it like company names, and contacts.

    I have another program that tells me what new quotes I have in our system. It let's me export to excel so I have it return the account number, the size of the deal, and the deal number to a workbook called OpenQuotes.

    I copy that information from OpenQuotes, and paste it into the left 3 columns of my Pipeline sheet. (A1) Account number, (B1) Deal size, (C1) Deal number. To the right of that, I use VLOOKUP and reference (A1) against my ClientInfo sheet to populate the corresponding company name, and contact information.

    At the end of the day, my Pipeline sheet looks like this:
    (A1) Account number, (B1) Deal size, (C1) Deal number, (D1) Company Name, (E1) Contact Name, (F1) Email Address, (G1) Phone Number

    The problem(s)
    My accounts have more than one contact person, and the proper contact person changes depending on who I am working with on each particular deal.

    So if I had 6 contacts at XYZ Corp, Inc, all of a sudden my Pipeline sheet is not stopping at (G1), it's going all the way out to (AA1). Way too much scrolling when the idea is to quickly glance at the sheet and know who I need to call for each deal.

    The second problem is that I have multiple accounts, and the deals are always being removed, added, or changed on a daily basis, so I can't figure out a formula to accommodate that.

    The solution?
    Ideally what I'd like to do is copy and paste the 3 fields from from OpenQuotes to Pipeline, have vlookup populate (D1) with the name as usual, and (E1) with a drop down for that particular account number, pick the correct contact name for the deal, and have that return the chosen name's corresponding email address and phone number.

    For example, it might look like this at the end:

    (A1) 1001 (B1) $10,000 (C1) QN2222 (D1) XYZ Corp, Inc (E1) <drop-down list of only XYZ names from ClientInfo sheet> Joe Dale (F1) <result based on (E1)> joe.dale@xyzcorp.com (G1) <result based on (E1)> 800-555-5555 ext 1234
    (A2) 1002 (B2) $65,000 (C2) QN2453 (D2) ABC Corp, Inc (E2) <drop-down list of only ABC names from ClientInfo sheet> Sam Farr (F2) <result based on (E2)> sam.farr@abccorp.com (G2) <result based on (E2)>800-666-6666 ext 4321

    How can I accomplish this?

    Thanks very much
    Last edited by shingudaze; 05-02-2014 at 04:05 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Best way to organize a pipeline?

    Better attach a sample file so that we can give/show you the exact solution


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    05-01-2014
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Best way to organize a pipeline?

    I think you're right

    Here is an example sheet of what I'm trying to accomplish, along with notes.

    Thanks!

    pipeline_example.xlsx

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Best way to organize a pipeline?

    Can you please show the expected output for 100001 account?

  5. #5
    Registered User
    Join Date
    05-01-2014
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Best way to organize a pipeline?

    Sure, I'm re-attaching it with the expected output. I put a standard vlookup in column D. What I don't know is how to get vlookup to return the dropdown in column E, so I did a standard data validation list just to show the desired output. Upon selecting the name from the drop down in E, I would like the email address and phone number for the selected person to populate accordingly.

    pipeline_example.xlsx

    Thanks again.

  6. #6
    Registered User
    Join Date
    05-01-2014
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Best way to organize a pipeline?

    Hi all, any update to this? Re-attached excel sheet in last post as requested.

    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sales pipeline- Dividing money across months
    By tfboland in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-21-2013, 05:08 PM
  2. Need some help with a sales pipeline report
    By tonycanevaro in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-13-2012, 04:02 PM
  3. Sales Pipeline
    By birdoo2 in forum Excel General
    Replies: 8
    Last Post: 07-15-2009, 04:29 AM
  4. Surveyor on pipeline
    By jneeb in forum Excel General
    Replies: 3
    Last Post: 10-29-2008, 11:21 AM
  5. [SOLVED] how do I set up a sales pipeline spreadsheet?
    By DrTimBoone in forum Excel General
    Replies: 1
    Last Post: 05-19-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1