Locating a Data Validation table

Copper 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.

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

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

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

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

=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.

 

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

@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.   

@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 and potential errors and security concerns. Let's learn about this exciting new feature.

@ddsmith52 I have the exact same problem that you had. How did you unhide the tab? The author of the spreadsheet I am working on seems to have hidden this tab in a way that a simple right click on a tab to unhide is not enough.