1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Drop down box how to view 2 columns

Discussion in 'Business Applications' started by suesues, Apr 26, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. suesues

    suesues Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    12
    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?
     
  2. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    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.
     

    Attached Files:

  3. suesues

    suesues Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    12
    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
     
  4. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Let me / us know how you get on. :)
     
  5. suesues

    suesues Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    12
    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
     
  6. suesues

    suesues Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    12
    form delux 2015 nebs 3.jpg
    am having trouble with the "description" drop down box or formula
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  8. suesues

    suesues Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    12
    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
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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?
     
  10. suesues

    suesues Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    12
    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
     
  11. suesues

    suesues Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    12
    oh that does not look right
    fertilizer applied is column one
    and the price is column 2 of table
     
  12. suesues

    suesues Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    12
    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
     
  13. suesues

    suesues Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    12
    I will send some "printscreen" photos to my newer computer to show!
     
  14. suesues

    suesues Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    12
    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.
     

    Attached Files:

  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, what was this originally, dBase?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1147281

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice