Solved

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

Posted on 2014-09-22
5
166 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now