How to crack an Excel workbook that looks up values?

Question

Alright I know this isn't 100% related to programming (the Excel book in question doesn't use VBA at all) but I'm hoping someone can help me out with this or point me in the right direction.

My boss got a spreadsheet from a vendor that has a combobox/dropdown list with various part numbers; when you select one it populates the rest of the form with a lookup containing additional items. I've been tasked with "cracking" this and finding the list that they're using to populate so we can make use of it.

The thing is... there's no VBA code, no macros, no data connections, and only one Worksheet displayed in Excel while the lookup code references a Sheet1. I've tried to display hidden worksheets and it says there are none... so where on earth could this list be kept? My boss is getting impatient and is asking me if I've broken it yet. It's not a big deal if it can't be done, I just have no clue where to continue looking for it and I don't know what to tell my boss when he asks me if I'm done.

Can anyone help?

Answer 1

It's possible to hide a worksheet using VBA so that it can't be unhidden from the UI.

Try the method for un-hiding all hidden workhseets outlined here:

http://www.automateexcel.com/2004/12/14/excel_vba_unhide_all_worksheets/

Answer 2

Could it be some data stored on the same sheet.
Possibly in columns which are either hidden, or which are far off the actual page?

Answer 3

Isn't this just data from the worksheet only?

Column header dropdown lists acts as filters, they show distinct values of a column. This is a feature of Excel.

Answer 4

The items could be cached from a currently unavailable resource. Try saving it out to xml and searching for a known string.

Answer 5

My guess is that it is a Data Validation list which references a constant list of values or a range on a "Very Hidden" sheet. In Excel 2007, select one of the cells with the drop-down, click on the "Data" tab in the Ribbon, click on "Data Validation" in the Ribbon and see what you have. In Excel 2003 it is the Data -> Validation... command.

Another possibility if you know the name of the worksheet is "Sheet1" is to add a new worksheet, enter "=Sheet1!A1" into cell A1 of the new worksheet, and copy this cell down and to the right for as large of an area as you need to see the data you are interested in.

If you can post a URL to download the workbook (assuming it is not a trade secret) you would be more likely to get an accurate answer.

Answer 6

1. Click on the cell that display a drop down list when selected
2. From the menubar select data>validation
3. In the dialog box copy the content of the source text box
4. Now paste the content on any empty cell on your worksheet
5. Select a drag it down to see the values populating the list

Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

Answer 7

I am assuming that you have broken this by now, but just in case you havent. This is certainly a case of data validation using a named range which is house on another sheet that was designated "very Hidden" from the vba console. You will need to open up the VBA project of this worksheet and designated the "very Hidden" sheet to just Hidden and then you will be able to unhide it, or the other setting at which point it will be viewable.