• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Excel, Validation, VLOOKUP

Using Data Validation to prevent invalid data from being entered into a cell.  Validation criteria is to allow only data from a List.

Values for the list are contained in a separate worksheet in the same workbook.  Is it possible to create a list of valid items using VLOOKUP?

VLOOKUP looks for a value in the leftmost column of a table, and then returns a value in the same row from a column that I specify.  It doesn't produce a list.
0
clock1
Asked:
clock1
  • 3
  • 3
  • 3
1 Solution
 
Katie PierceCommented:
Not entirely clear on what's giving you trouble, but check out this article:  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_17079-Creating-an-Efficient-Dashboard-in-Excel.html

It dives into using VLOOKUP across multiple sheets within a workbook.  Hope it helps!
0
 
FarWestCommented:
could you elaborate more about the problem
because you can select value range for list validation from other sheets in the workbook
0
 
clock1Author Commented:
Sheet 1 contains 3 entries in cells A1:A3.  Want list validation for B1:B3.  So, B1 list contains 2 items Onions, Turnips.  User can only pick from these 2 items as entries in B1.

Sheet1:
Col A       Col B
Red
Green
Blue

Sheet2:
Col A      Col B
Green    Apples
Green    Potatoes
Red        Onions
Red        Turnips
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
clock1Author Commented:
Reviewed the link and not making the connection using pivot tables.

My need is to control what the user chooses to enter in the cell.  Not to retrieve data.

Am I missing the point?
0
 
Katie PierceCommented:
Well, once you've done a pivot table to identify those specific results with the inputs, you can use that to be the name for a dropdown list.
0
 
FarWestCommented:
I have done similar thing, as I recall  using indirect function without pivot tables , sorry it is after midnight in my local time, I will get back to you after few hours
0
 
FarWestCommented:
attached is the solution,
but you need to add a column for the look up list that needs to be filled with ranges that belongs to column A,

good luck
Book1Lookup.xlsx
0
 
clock1Author Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now