Solved

Help with populating cells via VLoopUp or Other Method (open to suggestions)

Posted on 2014-09-22
5
170 Views
Last Modified: 2014-09-23
Hi experts

I need to Excel expert to guide me on the best way to do something.

Part 1:
I want to populate cell B1 of the 'Main Page' worksheet with a Company Name from Company Names in the 'Contacts" worksheet. I've used a VLoopUp which returns my the company names, but there are a whole heap of duplicates, which as the list grows will be annoying. I need to filter this list down more by selecting a contact name from the company, so I'm not sure if the best approach is to have a drop-down list of the company names and the contact, or to filter the company name list down so no duplicates appear, and then under cell B5 have a selection of Contact Names that only appear from that selected Company Name.

Part 2:
What function/s should I use to achieve the solution to part 1, I.e. can you have a Vlookup that displays to columns, or can you stop showing duplicates in the Vlookup? And in Cell B5 can I filter the drop-down list to only show names connected with the selected Company Names.

Help! I've attached my file.
Populate-Address.xlsx
0
Comment
Question by:Fi69
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40338545
There are a couple of ways one can go about this.  One way is to use VBA to create a list "on-the-fly" as data is changed.  Another way is to create dynamic ranges that will capture the unique data and present that in Data Validation.  I'm showing the latter method here.

First, to create a list of unique company names, an additional table will need to be created.  In it, the following formula would be inserted in the second row and copied down as far as needed:
=IFERROR(INDEX($A$2:$A$33,MATCH(0,INDEX(COUNTIF($G$1:G1,$A$2:$A$33),0),0)),"")

This will produce a list of unique values - company names - from your main table.  This is then made into an Excel Table and assigned the name "Table2".

Second, a dynamic range name is created to reference only the non-blank cells in this new table.  I'll call it "CompList" and give it the following formula:
=OFFSET(Contacts!$G$1,1,0,MATCH("",Table2,0)-1,1)

Third, we'll assign this range to the data validation in cell B1 of your "Main Page" worksheet.  Just change the Data Validation source to "=CompList".  Now, only the four companies are visible in the drop down.

Since only the company name is shown in cell B1, we'll need to show the valid contact names for the selected company in cell B5.  Again, we'll create a dynamic range name called "ContactList" and give it the following formula:
=OFFSET(Contacts!$A$1,MATCH('Main Page'!$B$1,CompanyName,0),3,COUNTIF(CompanyName,'Main Page'!$B$1),1)

Lastly, we'll assign this range name to the data validation for cell B5 (setting the Source to "=ContactList").  This ensures that only contacts aligned with the company selected in B1 will show up.

I've taken the liberty of removing the "Contacts" range name, as it was not needed in this example.  I've attached a modified file for your review.

Regards,
-Glenn
EE-Populate-Address.xlsx
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40338552
Please find attached.
Populate-Address.xlsx
0
 

Author Comment

by:Fi69
ID: 40338573
OMG Glen, that's fantastic. I'm going to look at it more closely now and try and understand the process.
0
 

Author Closing Comment

by:Fi69
ID: 40338737
Thank you!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40339565
You're welcome.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question