+ Reply to Thread
Results 1 to 11 of 11

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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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.

  10. #10
    Registered User
    Join Date
    04-28-2023
    Location
    India
    MS-Off Ver
    21H2
    Posts
    1

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

    Quote Originally Posted by avregjo View Post
    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.
    Hi avregjo I have the same Issue Can You Provide me the solution.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

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

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. 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