|First, let me say that I just edited my earlier response because I noticed that my example data didn't match in 2 sections of my post due to a typo. Only the example data was edited, not any of my formulae or text, and it does not make any difference in how the concept that I suggested works.|
Keep in mind that this is a concept and is based on my sample data. Obviously the formulae you use will need to be based on your data.
Now, to address your issues:
a) Drop down list is too narrow to see both the prod_id and the full title which can be 30-50+ characters
"The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list."
b) All that the formula
does is strip the title back from the concatenated field and put it in another cell..
No, that is not what is does. It looks up the prod_id in the table_array in Booklist.xls and returns the title from that sheet.
The reason it appears to just "strip the title back" from the concatenated field is because the title is what is in the 2nd column of the table_array in Booklist.xls.
Look at the sample data I provided.
The original data is in Column A (prod_id) and Column B (prod_title) in Sheet1 of Booklist.xls
This data in concatenated on the invoice sheet and placed in the drop down list, which in my example is in B1 of the Invoice sheet.
The LEFT(B1,FIND(" ",B1)-1 portion of the formula returns the left most characters up to, but not including the space. This is the prod_id in my sample data. This then becomes the lookup_value for the VLOOKUP.
The VLOOKUP then looks up that string in the table_array and returns whatever is in the 2 column.
If you want to prove this to yourself, add a 3rd column to the table_array and change the formula so that the table_array argument is $A$1:$C$3 and the col_index_num is 3. It will then return whatever is in the 3rd column in Booklist.xls proving that it really is looking up the prod_id.
re: What I need is to be able to see both the prod_id and Prod_title in the drop down list, be able to select the one I need and put in the validation cell just the prod_id.
If the "validation cell" is the same cell as the one that contains the drop down (you know, the one created via Data...Validation) then I don't think you can have it both ways - at least not easily. The only way I know of to make the cell wide enough to show both the prod_id and prod_title but only show the prod_id once it is selected from the list is with VBA. Widen the column when you select the drop down cell and make it narrow after the item has been chosen. VBA code could be written for that.
However, keep in mind that just because it only shows the prod_id when narrow, it still contains the prod_title and that has to be stripped off (with the LEFT function) so that the VLOOKUP only looks up the prod_id.
Another option is to have a separate cell that will hold the prod_id once the item is chosen from the drop down. Keep the drop down list wide, use the LEFT function in another cell to extract the prod_id from the longer string and then use that cell as the lookup_value for the VLOOKUP function.
re: I still have to find ways of making drop down validation table wider.
Asked and answered.