Excel Drop down box how to view 2 columns

[suesues]

have designed an invoice form in Excel 2010
have made a Drop down box using the "products" worksheet information in the drop down list. The drop down list shows only column 1.

some ITEMS in the "products" worksheet, have the same name (col 1) with different prices in (col 2)
for example:

the following is an example of the "product" list:
col 1 col 2
apple $44
apple $48
apple $50

I would like to view columns 1 and 2 in the drop down list to make the choice of which price to charge

depending which row "apple" is chosen from, the price goes into the amount column on the invoice, with the description showing only "apple"

I wish to only show "apple" in the invoice description area

so ... how to view 2 columns (in the drop down Box) but only make one entry (from col 1) into the invoice?

 

[CodeLexicon]

It sounds like you would need to set up a third column in your sheet to combine the Col1 and Col2 information and then link your drop down to that. Assuming your drop down tool is an activex or forms type rather than "in cell validation" type, you can then fill your invoice with the information from Col1.


See attached.

 

[suesues]

that is an ingenious idea and would work (I tried and tried and worked on it all day) if I did not need the col 2 to make "other" entries into also:

I am still working on it
your idea is one had had not tried yet

 

[CodeLexicon]

Let me / us know how you get on.

 

[suesues]

Let me / us know how you get on.

well I will try to clearly explain what I am trying to accomplish!

have used a form filling invoice software for the last 20 years - a very simple DOS based software that does a "fill in the form " from NEBS (they are not in business for many years now).
The program is so old, I have to keep a separate computer just for this software as only a dox matric printer will work with this DOS program and windows 7 will not work well with the DOS program either. I have found solutions to those little annoying problems -- but when they stopped making ink cartridges for the (old) printer -- I finally admitted there must find another solution....

I have started to "re-write" the software formulas into excel - to "recreate" the old software program -- and am making a lot of progress -- but some things greatly elude me!! I have searched the "net" and found this "tech support guy" website and have studied many entries. Here I am now trying to find a solution......
I very much appreciate your help! Susan

 

[suesues]

am having trouble with the "description" drop down box or formula

 

[Zack Barresse]

I would NOT recommend using an ActiveX control. Using an in-cell data validation drop down would suffice. Assuming each key/pair value is unique, you can then use that to either get the description and/or amount, or even simply just split the data on the last space, or dollar sign, or whatever.

Do you have a sample file you're working on which you can post? I'd rather not create one from scratch.

 

[suesues]

I am recreating the software for the invoice form (shown earlier) in excel EXCEL 2010 ... The old DOS software is a formfilling software.

in the current software(DOS based) an F3 key is used to show a list of possible entries to "fill in" the description and amount area. The user (me) creates the data list. Once that is done, a simple one key F3 brings down a list choices to pick from and enter into the form on a "description" line.

Here is where I am having a problem: In my new rewritten excel workbook --- If I enter something that is not in the created data (table)(list) I can do so, but then when I enter a price for the new entry into the amount column I am writing over the VHOOKUP formula and it gets deleted ...
this is the formula I am using in the amount column that is based on the description entry (from drop down box)
=IF(ISERROR(VLOOKUP(D24,product!$A$1:$C$630,2,FALSE)),"",VLOOKUP(D24,product!$A$1:$C$630,2,FALSE))

this works just fine - but only if I use an entry from the drop down list.
If I chose to make an entry not on the drop down list and must put a price in the amount column the VHOOPUP does get deleted

I think I maybe need 2 dropdown lists? one in column "description" and one in "amount" column ---- ah I feel I am rambling now

 

[Zack Barresse]

I understood your original post, that you have items which may have multiple prices. So you may choose product "Apple", but you should either have 1) a place to manually input a price, or 2) have a drop down of associated priced from a pre-populated list you have created.

Can you post a sample file?

 

[suesues]

I think I have 2 problems now!

this is 1st problem:
I would like the drop down box to show 2 columns - rather than just the first column, because the entry in column 1 is the same for many items but with different prices in col 2:
this is an example of part of my table: different prices correspond to the same entry ... so when I look in the drop box list they all say the same thing "fertilizer applied" so how am I to know which entry to chose from to get the correct price unless I try them all one at a time (that is not the correct way to do that)
this is problem one.

column 1 of table column 2 of table ( this has the Vlookup formula in this column)
Fertilizer applied $56.00
Fertilizer applied $57.00
Fertilizer applied $58.00
Fertilizer applied $59.00
Fertilizer applied $60.00
Fertilizer applied $61.00
Fertilizer applied $62.00
Fertilizer applied $63.00
Fertilizer applied $64.00
Fertilizer applied $65.00
Fertilizer applied $66.00
Fertilizer applied $67.00
Fertilizer applied $68.00

 

[suesues]

oh that does not look right
fertilizer applied is column one
and the price is column 2 of table

 

[suesues]

The old DOS software .... would let me make an entry in "description"

and if I chose to use a look up (the F3 key) I could chose an entry from the look up, push enter and it was there, and so was the price for the item (if I had inputted that info into the field)
or
I could use the entry and still change the "amount" to anything I chose

the price cell does not change whether I input my own number or use the look up from the drop down list of (F3)

also if I chose to just type in any description at all and put in a price to the amount col that was ok too

it is/was very versatile .....
hmmmm ... maybe I can post a "pic" of the old data base look up chart from the old software

 

[suesues]

I will send some "printscreen" photos to my newer computer to show!

 

[suesues]

this is a photo my old software, this is the page I enter my invoice data, I am creating an excel worsheet to mirror this old software. The 2nd photo is a photo of the "pop-up (or) drop down box items that can be entered in the description area.

 

[Keebellah]

Hi, what was this originally, dBase?