Greetings Gurus,

I have a situation to which I had a solution, unfortunately my code got wiped out and I can no longer track down a fix to my problem. What I have is a worksheet in Excel 2003 that is used to track the hours worked against a particular project. Each project has a production code that is used to bill the client for the hours worked. I am using a dynamic named list that I have called 'CurrentProdCode' each of these production codes has a related name for the project. I have a named list for these project names called 'SuperSytem'. Because the projects will change production codes at certain benchmarks in the project, it is necessary to sort my list by 'CurrentProdCode' vice the 'Supersystem'.

What I am looking to do is validate the production code using the named list. That part is simple, where I run into the difficulty is that the product codes don't really give the user useful information as some of them only vary by one digit, so what I am trying to do is mimic the way a combo box in Access can show numerous fields but only store the "bound column" in this case the 'CurrenttProdCode'. In other words when the user clicks on the in-cell dropdown list they will see:

107231..01 Database Development

What will be stored is just the 107231..01. As I said, I had a solution but the code got wiped out and I cannot find any examples of how to get around this. Any help would be greatly appreciated.


Rob

You are probably going to need two fields: one for "show" and one for "go."

The show field will have the user friendly text (E.g. 02 - Description 02). The field that you actually want to use (For example, Code) can be gotten via a VLOOKUP in another cell.

That was the premise behind the previous solution, but I don't remember it being a VLookup, the solution had been done in VBA. Now that is neither here nor there, so long as it works, but how do I incorporate the VLookup into Validation?

You don't incorporate it in the same field. I am assuming that the purpose of the dropdown display is to give the user something meaningful to read and that the prupose of the dropdown value is to be used in a calculation somewhere else. However, the what appears in the dropdown list must be translated to another value before it can be used. Use the VLOOKUP in a separate cell (perhaps even a hidden cell).

Suppose the dropdown list is in Cell B2. then use a validation to look at the Dropdown Values. In some other cell, use the formula =VLOOKUP(B2,Table_Lookup,2,False) assuming Table_Lookup contains the data in the picture attached to my last post. Then use the value in the other cell to do the calculations.