Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-09-22
5
Medium Priority
?
177 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

578 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