+ Reply to Thread
Results 1 to 5 of 5

filling in data based on drop down list

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    Eureka, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    3

    filling in data based on drop down list

    Excel 2010

    I am completely stumped and extremely frustrated. Here is my problem (probably something easy, but my brain hurts too much to figure it out):

    I have a MASTER sheet that I use to create quotes. It contains all of the components of a building in column B. I want column C to be a drop down menu of lumber choices. Upon selecting the appropriate lumber for the job, I want Excel to automatically fill in column A with the supplier and column M with the price of that said item (D-L have other stuff not concerned with this task). In another sheet, DATA, I have listed the supplier of the lumber (A), the lumber to be selected from the drop down menu (B), and the price (C). I have made the drop down list just fine, but I cannot get Excel to fill in column A or M with the appropriate data. All it returns is "N/A"

    Here is the formula I am working with. =IF(ISBLANK(C36),"0.00",VLOOKUP(C36,DATA!A:C,3,0))

    When I select 2x4x8 from the drop down list in C, I want Excel to fill in SHOP in A and 2.19 in M.

    I do not want to use macros if at all possible.

    Any thoughts?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: filling in data based on drop down list

    that looks like it should work (a simplified version would be =IFerror(VLOOKUP(C36,DATA!A:C,3,0),0) - unless you want the 0.00 to be text?)

    can you upload a sample work book for me to take a look at?

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-11-2012
    Location
    Eureka, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: filling in data based on drop down list

    Thanks for the reply.

    I do want 0.00 not just 0 or blank. I have no preference how that is achieved.

    I have uploaded the file. As you may notice, because I certainly did this morning, Excel throws a fit when you open it--something about protected view. Probably has something to do with my weird formula. So after we get everything working, I would hope that warning goes away.
    Attached Files Attached Files

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: filling in data based on drop down list

    Maybe in A36 and copy down.

    =IF(ISBLANK(C36),"0.00",INDEX(DATA!$A$1:$A$30,MATCH(MASTER!C36,DATA!$B$1:$B$30,0)))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    10-11-2012
    Location
    Eureka, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: filling in data based on drop down list

    Thanks Fotis. This did work for the time being. We are experimenting with better ways to do our quotes and avoid mistakes. This eliminates the 2 most common mistakes we have--not changing the supplier and price when changing lumber size. This will also make it a ton easier when we have price changes or start stocking additional lumber sizes. We have a ways to go, but it is a step in the right direction! I wish Excel had a more straightforward way of doing this....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1