+ Reply to Thread
Results 1 to 22 of 22

Data validation list doesn't update the choice if the source cell content is changed

  1. #1
    Registered User
    Join Date
    03-03-2013
    Location
    bccb
    MS-Off Ver
    Excel 2003
    Posts
    15

    Data validation list doesn't update the choice if the source cell content is changed

    Hello,

    There is a drop-down data validation list which takes the data from source cells on the second sheet. The list is used to show the group a word belongs to and is used for every word. However if I decide to change the name of a group, I change it on the second sheet and the old name remains on the first page until I manually choose the new title from the drop down list. Is there a way for this update to happen automatically? I will be grateful for any ideas on the subject.
    Last edited by bccb; 03-07-2013 at 04:28 PM.

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Hi -

    Can you upload your spreadsheet so we can take a look at it?

  3. #3
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Try naming the range of the source data, Data validation doesn't like ranges from other sheets. Name the range use the =RangeName for the data validation list range.

  4. #4
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    I am working on this for you.

    I am really close.

    Watch this space.

  5. #5
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Ok I have good news and bad news.

    I could only get this working using a couple of macros.

    The first macro is hidden in sheet 2 and tuns whenever you change any data in sheet two.
    However when you change the data that your Data Validation uses, its lost forever, so I have to copy that data in P1 to P10
    I also have to save the text in cell A1 somewhere before anything else.

    To see tis macro rigt click on the tab sheet2 at the bottom of excek and select view code.

    The second macro deletes and then recreates the data Validation in Cell A1, I could not get VBA to modify the existing Data Validation.

    To see that macro called Data validation select macros then edit.

    You should be able to modify my macro to work on your workbook. I would recomend that you dont use Temp and Temp2 as variables because if you are likely to use those names elsewhere and ruin the operation of these macros.


    Enjoy.

  6. #6
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    here is my sample file
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-03-2013
    Location
    bccb
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Hello,

    Thank you for your replies and sorry for being unable to write earlier. Thank you very much for the file and your solution, I am not quite sure I understand the function of P1:P10, but I will look through it carefully. Thank you for your time and help
    Last edited by bccb; 03-10-2013 at 07:09 AM.

  8. #8
    Registered User
    Join Date
    03-03-2013
    Location
    bccb
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Data validation list doesn't update the choice if the source cell content is changed

    I tried several times changing the name and it works great, however, from time to time this error occurs

    Run-time error '91':
    Object variable or With block variable not set

    Selection.Find(What:=temp, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

    If I close it without saving and re-open it works fine, but once this error occurs it stops. Could you help me with any ideas on this? Also from time to time when I open it, even though the macros are enabled, it acts as if they aren't and doesn't update the information

    P.S. this is on the same file, without any modifications made

  9. #9
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Ahh

    I see what you are doing.

    The area P1:P10 stores your original data so that you know what has changed. Do not play with this.

    Eg
    on sheet one you select 3
    on sheet 2 you change 3 to Three
    on sheet 1 you still see a 3
    so what cell has change on sheet 2? the fact that you can see 3 dosn't help. So you need to know that 3 was in line 3. So look for 3 in P1:P10.
    So you know that you were showing 3 which is in line 3
    So now you tell the data validation to look at the third cell in A1:A10 which is ................THREE.
    I Think I programmed the macro to update P1:P10 automatically so you shouldn't change anything there.
    If you do, then the macro can't find what you original value 3 and will crash.

  10. #10
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Please Login or Register  to view this content.

  11. #11
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Something else you should note is the changeflag in the code

    when working with userform and spreadsheet it is so easy to get into a loop.

    Sheet change macro starts and modifies the sheet, so that starts of the sheets change macro which modifies the sheet, so that starts of the sheets change macro which modifies the sheet, so that starts of the sheets change macro which modifies the sheet, so that starts of the sheets change macro which modifies the sheet

    ad infinitum.

    So if the flag is one, you skip the part of the sheet that modifies the sheet thus avoiding a loop. But when you finish the flag must be set to 0 otherwise the macro will never work.

    Guess What? That is why your macro never works after you crashed it. if you had run the line
    Please Login or Register  to view this content.
    Then you wouldd not have needed to close the macro. and reopen it.

  12. #12
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: Data validation list doesn't update the choice if the source cell content is changed

    I may not be understanding your question but take a look at the attached and see if it works like what your looking for. It have one small VBA sub so if you change the name in cell B2 cell D2 gets set to blank ""
    Attached Files Attached Files
    Tom S.
    ↙ If you find my reply helpful click on the * down there on the left. Yeah that's it, right there, down on the left
    If your question is resolved, mark it SOLVED using the thread tools.

  13. #13
    Registered User
    Join Date
    03-03-2013
    Location
    bccb
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Hello,

    Thank you for your explanations, however, I am not sure what I should do. I haven't changed anything in the original file, I opened it several times to see how it goes, sometimes it works fine, sometimes it doesn't work at all even though the macros are enabled, and sometimes it works well at the beginning, but when I change the value in some of the A-cells on sheet 2 it ends with an error and shows me this part of the code as an explanation:

    Run-time error '91':
    Object variable or With block variable not set

    Range("P1:P10").Select
    Selection.Find(What:=temp, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

    This all is on the original file, without any changes to the code or the P-cells.

  14. #14
    Registered User
    Join Date
    03-03-2013
    Location
    bccb
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Tank997, Thank you for trying to help, my issue is slightly different. if your file serves as an example, my question is, if you choose a name from the drop-down list and then decide to change the name in the source cell on sheet 2, how can the cell on sheet 1 get the updated name without having to choose the updated name manually from the drop-down list.

  15. #15
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    I will try and replicate the error.

    I will come back to you if I can. Hopefully with a fix.

  16. #16
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    I am sorry I don't get that error

    this is the file that I have been using.

    Please try this and let me know if it persists.
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    I cannot replicate the error

    this is the file I used. Please try again.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: Data validation list doesn't update the choice if the source cell content is changed

    I was able to duplicate the error by entering a value in A7 on sheet2 and then deleting it, then selecting the data validation on sheet1 and selecting a blank below the six. This caused Cell O1 on sheet 2 to get out of sync, so to speak with the list in column P. One thought is if you made the data validation list dynamic it should not allow invalid rows from sheet 2. Just my 2 cents...

  19. #19
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Thanks Tank.

    I assumed that the user would be over typing the data.

  20. #20
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Data validation list doesn't update the choice if the source cell content is changed

    improved error checking
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    03-03-2013
    Location
    bccb
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Thank you very much! That's just perfect! Could you give me some directions on how to transfer the script and use it in other documents, please?

  22. #22
    Registered User
    Join Date
    03-03-2013
    Location
    bccb
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Data validation list doesn't update the choice if the source cell content is changed

    Thank you for your help Tank!

+ 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