+ Reply to Thread
Results 1 to 9 of 9

Excel Data Validation not working after close and reopen even as xlm file

  1. #1
    Registered User
    Join Date
    07-03-2015
    Location
    Thailand
    MS-Off Ver
    2010
    Posts
    9

    Excel Data Validation not working after close and reopen even as xlm file

    I have a large worksheet called "Products" which lists all our building materials which are organised with 3 categories and then the product description i.e.

    Cat1 Cat2 Cat3 Product Name
    Accessories, Decking, Wood, Wood Plank 001
    Accessories, Decking, Wood, Wood Plank 002
    Accessories, Decking, Plastic, Plastic Plank 001
    Accessories, Decking, Plastic, Plastic Plank 002

    I have defined the categories on another sheet to list all the available options and then made a 3rd worksheet to enable staff to choose materials based on drop boxes.

    I have done the first 3 list boxes as cascading list boxes which filter the categories OK. This all works fine. I then use data validation on the fourth cell to choose the product description using the following formula

    =OFFSET(Products!$D$2,MATCH(1,(Products!$A:$A=B8)*(Products!$B:$B=C8)*(Products!$C:$C=D8),0)-2,0,COUNTIFS(Products!$A$2:$A$417,B8,Products!$B$2:$B$417,C8,Products!$C$2:$C$417,D8))

    The worksheet all operates perfectly. However when I Save and Reopen the last part (fourth cell with the product description) does not work anymore. BUT if I simply click data validation and then OK (Without changing the formula or anything) it works again fine.

    I have tried saving as an xls file AND the newer xlxs file AND the Xlm file with all macros enable. Nothign changes.

    Every time i must open the data validation and click OK again and it will work until I close the worksheet again.

    I am Stumped! Any one can help?

    Thanks

    SF

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Excel Data Validation not working after close and reopen even as xlm file

    Can you attach a desensitised sample showing the problem?

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Registered User
    Join Date
    07-03-2015
    Location
    Thailand
    MS-Off Ver
    2010
    Posts
    9

    Re: Excel Data Validation not working after close and reopen even as xlm file

    Sorry forgot to attach the file. I have attached it now.

    Once you open it the Data Validation on the BOQ sheet will not work BUT if you just click Data Validation then OK it will all work again.

    Thanks
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,213

    Re: Excel Data Validation not working after close and reopen even as xlm file

    OK the way I do cascading DV is to have a named range for EACH category/sub-category using the name from the level above, etc, then I use INDIRECT to grab the name from the previous cell.

    I have changed a few of your DV's to what I suggested, you will need to create named ranges for all the others.
    Attached Files Attached Files
    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

  5. #5
    Registered User
    Join Date
    07-03-2015
    Location
    Thailand
    MS-Off Ver
    2010
    Posts
    9

    Re: Excel Data Validation not working after close and reopen even as xlm file

    Hi FDibbins, yes I understand that but that was never the issue. The issue was when I close and re open the Data Validation in Column E stops working. But if you select data validation, click OK then it all works again until you close and re open.

    The cascading lists already worked fine (although your way is probably more tidy) but on your spreadsheet you have the same problem with Column E after close and reopen.

    For some reason i cant solve this and dont know whats wrong. Everything works fine until close and reopen

    Any other ideas ? Its driving me crazy :-)

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,213

    Re: Excel Data Validation not working after close and reopen even as xlm file

    OK, what is the basis for selecting the 4th category?

  7. #7
    Registered User
    Join Date
    07-03-2015
    Location
    Thailand
    MS-Off Ver
    2010
    Posts
    9

    Re: Excel Data Validation not working after close and reopen even as xlm file

    If you re click the data validation it will all work correctly. The fourth column selects the products based on the first 3 cascading lists. As mentioned it all works fine if you refresh the data validation. Until you close the workbook

  8. #8
    Registered User
    Join Date
    09-03-2019
    Location
    Manila, Philippines
    MS-Off Ver
    Excel for Mac
    Posts
    2

    Re: Excel Data Validation not working after close and reopen even as xlm file

    Hi jinjok.
    I have the same problem.
    Have you been able to solve this?
    my DV formula that breaks on reopen of file is:

    =IF($B11="Sales Marketing Company", OFFSET(Sheet4!$D$1,(MATCH(1,($C11=RegionList)*($D11=MktgList),0)),0,COUNTIFS(RegionList,$C11,MktgList,$D11),1), OFFSET(Sheet4!$D$1,(MATCH($B11,DeptList,0)),0,COUNTIF(DeptList,$B11),1))

    It is the 1st OFFSET that breaks.

  9. #9
    Registered User
    Join Date
    09-03-2019
    Location
    Manila, Philippines
    MS-Off Ver
    Excel for Mac
    Posts
    2

    Re: Excel Data Validation not working after close and reopen even as xlm file

    Want to report that my problem has been fixed. Had to create a Named Range containing the OFFSET formula, then use the name in the DVList.

+ 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] VBA stops working.... I have to close and reopen to start again
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-13-2017, 10:54 PM
  2. My Excel SQL connection won't refresh after I close and reopen file
    By Cristian888 in forum Access Tables & Databases
    Replies: 7
    Last Post: 03-29-2017, 08:43 AM
  3. Axes lable change to numeric value as i close and reopen file...
    By meus in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-12-2015, 07:08 AM
  4. [SOLVED] How to save sheet data, close workbook, then reopen Original
    By SCHammer843 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2014, 01:11 PM
  5. Close/Reopen Excel using Taskkill - possible?
    By lordterrin in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-17-2014, 04:36 PM
  6. locking cells after entry code does not work after close and reopen file
    By var in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-16-2012, 10:12 PM
  7. [SOLVED] create chart /table excel-save, close & reopen colors change? Why
    By Anne in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-04-2006, 04:20 PM

Tags for this Thread

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