Using data validation to pick Item number

[tewald82]

First sheet is where our evaluator enters item numbers, I currently vlookup filling in the description and cost based on item#.

Living Room
Item# | Description | cost | install cost ect.........
330 | Cordwraps | $1.25 | $1.00
2263 | Tip Resistant Furniture Tether | $3.00 | $2.50


Second sheet:

Product sheet:
Item # | Discription | Cost | install Cost | Prodlist (used for data validation)
66 |Professional Furniture Straps | $8.00 | $10.00 | 66 -- Professional Furniture Straps

I have a data validation drop down showing prodlist (66 -- Professional Furniture Straps) to help evaluators pick item based on the description if they don't know the item#. My vlookup only works on (item#).

How can I get the dropdown that shows (66 -- Professional Furniture Straps) insert just the "Item number" (66) when selected?

Thanks
Terry

[protonLeah]

To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[JBeaucaire]

To do this without VBA will require you to do some legwork in your main Sheet2 listing that acts as the source for the Data Validation. For this to work the values in sheet source range must contain NUMBERS ONLY (this is important, this trick does not work with text strings).

The way we accomplish this is by creating a custom Number Format for each and every cell in our source range. It's a bit of work, but gives you the exact results you want.

For example:

1)on Sheet2, in A2 and A3 you could enter the numbers

66
67

The numbers must be unique, of course.

2) Now click on A2 and press CTRL-F1 to open the Cell Format window.

3) On the Number Format tab, create a CUSTOM number format of "66 -- Professional Furniture Straps" including the quotes shown.

4) Repeat with A3 and apply a custom number format of "67 -- Something Else"

5) Now those cells APPEAR to have the text values you entered showing in the cells when you look on the sheet, but if you click on the cell and look in the Formula Bar you still see the only thing IN the cells is the numbers you typed originally.

6) Now create a data validation drop down list using these cells as the source.

7) When you USE the drop down, you will see the TEXT values in the drop down, but when you make a selection, it actually only brings into that cell the VALUE from the source cell, so you will get the number only.

Pretty slick trick, huh?

So, get to work, it will take a while to create the custom number formatting for each cell in your source range, fortunately you only have to do it once.

[tewald82]

That worked great, but some item# have text in them and the original custom numbering's not working. I've tried adding "@" in numerous places but still nothing.

G1000 -- Center Gateway Pressure Gate

Thanks