Locating a Data Validation table

%3CLINGO-SUB%20id%3D%22lingo-sub-772151%22%20slang%3D%22en-US%22%3ELocating%20a%20Data%20Validation%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772151%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20I%20locate%20a%20drop-down%20list%20in%20an%20excel%20document%20that%20someone%20else%20created%3F%26nbsp%3B%20I've%20been%20given%20a%20document%20that%20was%20already%20set%20up%20and%20I%20need%20to%20make%20changes.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-772151%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772180%22%20slang%3D%22en-US%22%3ERe%3A%20Locating%20a%20Data%20Validation%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772180%22%20slang%3D%22en-US%22%3EUnder%20the%20home%20tab%20%26gt%3B%20editing%20section%20%26gt%3B%20magnifying%20glass%20%26gt%3B%20%22Go%20To%20Special%22%20%26gt%3B%20Data%20validation.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20should%20highlight%20the%20cell%20that%20contains%20data%20validation%20for%20you.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

How can I locate a drop-down list in an excel document that someone else created?  I've been given a document that was already set up and I need to make changes.

8 Replies
Highlighted
Under the home tab > editing section > magnifying glass > "Go To Special" > Data validation.

This should highlight the cell that contains data validation for you.
Highlighted

@PReagan   Correct, but I am looking for the source of the list?  It is somewhere within the workbook, but I cannot locate it.

Highlighted
Click on the drop-down list > under Data Tab click Data Validation > Under Settings you should see the Source of the list.
Highlighted

=Table!$A$2:$A$7  This is the area in which the drop-down list is used, not the source of the list.   It is hidden somewhere in the workbook.

 

Highlighted
I apologize but I must be misunderstanding your question. Would you mind sharing your workbook so that I may be able to understand your question a little better?

**Please omit any sensitive/confidential information
Highlighted

@Ben Schorr   Thank you.  The mystery has been solved.  The author of the document had hidden a tab where the drop-down list was located.  When opening the Data Validation dialog box it referenced "=Table!$A$2:$A$7, but I wasn't able to locate the word "table" in searching each tab.  A co-worker showed me how to unhide the tabs.   

Highlighted

@ddsmith52 

Hi

For situations like this (and MUCH more complicated) there is a Tool that comes for Rescue: The INQUIRE Tab of the Ribbon:

This tool will enable you to find a Table, data Validation, a Formula, Errors, not only if the Sheet is Hidden but even if the Sheet is VERY Hidden (VBA Term)

To add the INQUIRE TAB:

  • File >> Options >> ADD Ins
  • Manage (lower left corner) >> COM Add-ins >> Go
  • Check INQUIRE >> Save the File >> Click on WORKBOOK analysis report...

 

Here is a short tutorial about the Magic of the Inquire Tab

https://www.youtube.com/watch?v=yOHJJcXtQq4

Hope that Helps

Nabil Mourad

 

Starting from Excel 2013 we have a new feature known as Inquire. With Inquire you can analyze, audit and review your workbooks along with data dependencies a...