+ Reply to Thread
Results 1 to 5 of 5

Drop down list in order form doesn't work when customer info cells are filled out

  1. #1
    Registered User
    Join Date
    04-28-2011
    Location
    Seattle WA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Drop down list in order form doesn't work when customer info cells are filled out

    I have an order form worksheet that uses a drop down list from a named range in another worksheet, PriceList. Above the order area are some blank cells for entering the customer name, shipping address, etc. The PriceList worksheet has a data connection to an Access database.

    My problem is, if the customer info section is filled out first, the drop down list of item numbers is empty. In order to fill out the form, the salesperson is having to enter all the items, save the file, then open it up again to enter the customer info. Can someone tell me why this is happening?

    This the code I'm using for the item number lookup, to populate the item description and prices:

    Please Login or Register  to view this content.
    ScreenShot001.jpg
    Last edited by JBeaucaire; 08-27-2014 at 07:31 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

  2. #2
    Forum Contributor
    Join Date
    07-29-2014
    Location
    Oz
    MS-Off Ver
    2010
    Posts
    142

    Re: Drop down list in order form doesn't work when customer info cells are filled out

    The screenshot and your code don't really help to determine your issue. You haven't mentioned how your named range data is populated. Is the Access database connection refreshed and/or filtered according to what is on the sales order form? If your data validation is changing, it is because the data being referred to in the named range is changing.

    It is hard to help properly without a working file that represents your issue. If you can post a workbook, You will less generalized help.

    Dan
    Don't forget to ☆ me if I helped you!

  3. #3
    Registered User
    Join Date
    04-28-2011
    Location
    Seattle WA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Drop down list in order form doesn't work when customer info cells are filled out

    Dan, the drop down list is populated by a named range in another worksheet called PriceList. The data in PriceList is refreshed from the Access database, not the other way around. The reason I'm perplexed is because the customer info area cells are just blank, no data validation whatsoever; these cells should not be affecting the Item # drop down list cells at all. Maybe I'm referencing the cells incorrectly or something. File is attached.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-29-2014
    Location
    Oz
    MS-Off Ver
    2010
    Posts
    142

    Re: Drop down list in order form doesn't work when customer info cells are filled out

    Hi Nokamoto,
    I've had a good look at your file. I can't replicate your issue. I filled out the customer section (I even tested having Item#'s as part of the customer number and address). But the data validation in the Item # was all there.

    I noticed there are a number of cells in the Item # column of your form that have no data validation attached. (It's one or two cells so often. Some examples are B30:B31 then B102:B103 then B174:B175 then B246:B247 and so on). Could it be that you were looking at a couple of these cells. You can just copy the data validation down from the top to the bottom again and see if that fixes your issue.

    Also, your named range "ItemNo" is returning #REF!, but I don't see it being used anywhere, as your Item # column data validation is using the "MASno" list.

    Is there ever a time when the access query returns a blank table (no data)? That's the only thing I can think would cause your named ranges to be empty, and therefore your data validation would be empty too.

    Dan

  5. #5
    Registered User
    Join Date
    04-28-2011
    Location
    Seattle WA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Drop down list in order form doesn't work when customer info cells are filled out

    Dan, thanks a lot for taking a look at my file. The PriceList worksheet is never blank. The data will refresh from the Access database if there's a network connection, but otherwise it contains the last loaded version. I'm wondering now if having a live connection to our server - where the Access file lives - is causing a problem. I'll go ahead and do some general cleanup on the cells and references and see if that helps.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Auto Populate Order Form, based on qty's filled in Price list - Query
    By magpie10 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-11-2014, 02:08 AM
  2. Updating a pivot table with VBA from Drop down list (1st item doesn't work)
    By tray262 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-25-2013, 02:32 PM
  3. VBA code for (un)hide columns related to drop down list doesn't work
    By maumon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-21-2010, 08:27 AM
  4. Purchase Order Customer Info Autopopulated
    By lj123 in forum Excel General
    Replies: 5
    Last Post: 09-29-2008, 05:00 PM
  5. Order form drop down list issue
    By TampaPromoGirl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2008, 03:41 PM

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