+ Reply to Thread
Results 1 to 13 of 13

Dynamic Data Validation

  1. #1
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    252

    Dynamic Data Validation

    Is there a way to make the data validation dynamic. In other words, if I update the validation list can it auto update the cells that are tied to it? Right now if I update the list the original data does not change, which is causing havoc with the formulas I have tied to it.

    Thanks in advance.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Dynamic Data Validation

    Hi Perk,

    You can create a Dynamic Named Range and then use this name for your validation list range. Other than that, I don't know exactly what you mean by "updating the original data". Can you attach a sample to show what you are trying to accomplish?

    Here are some links to DNRs.

    http://www.ozgrid.com/Excel/DynamicRanges.htm
    http://www.vertex42.com/ExcelArticle...ed-ranges.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    252

    Re: Dynamic Data Validation

    Thank you. Attached is my example. Test2 Beta Inventory System v2.1.xlsb

    By way of an example, if I change cell A4 in the Client-Sales Validation List tab from "University of Florida" to "University of Southern Florida", the name stays "University of Florida" in cell B8 in the resulting "Sales Rep Forecast Input" tab. Ideally if I change the name of a record in the validation list I would like it to auto populate in the resulting cells.

    Let me know if that makes sense.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Dynamic Data Validation

    OK,

    I think I see what you're after now. I don't know how to respond other than, this isn't how Excel works. A validation list makes sure you are putting a correct value into a cell. It doesn't have a formula with it.

    I think you are wanting something like Cascading Updates that is found in Access. The idea there is if a person changes their name you can change it in one place and it will be updated in all downstream places.

    In your problem you need to have a formula in B8 that points it back to A4 to do what you want. Then the idea of a Validation list isn't needed. It is like you are asking for things that don't yet fit together.

    Maybe one of the smart Guru's will have a better idea and help with this one.

  5. #5
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    252

    Re: Dynamic Data Validation

    Thank you trying. I will wait to see if someone has a trick up their sleeve.

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Dynamic Data Validation

    Quote Originally Posted by Perk1961 View Post
    Thank you. Attached is my example. Attachment 426819

    By way of an example, if I change cell A4 in the Client-Sales Validation List tab from "University of Florida" to "University of Southern Florida", the name stays "University of Florida" in cell B8 in the resulting "Sales Rep Forecast Input" tab. Ideally if I change the name of a record in the validation list I would like it to auto populate in the resulting cells.
    1. Select A2:A105 in Client-Sales Validation List
    2. go to INSEET tab, click Table
    3. Go to DATA tab unclick Filter
    4. Copy:
    Please Login or Register  to view this content.
    5. Go to Sales Rep Forecast Input
    6. Select B8
    7. Go to Data ==> Data Validation, change source with copied code, OK
    8. Double click on the little square on the bottom right of selected cell.

    to check:
    change or add anything in your Client-Sales Validation List (A2:A105)
    back to Sales Rep Forecast Input
    check first or any other DropDown cell.

    hope that is what you want.

    sandy
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,679

    Re: Dynamic Data Validation

    As well described by MarvinP, Validation (alone) donít have the capability to do the cascading sort of things.

    Use VBA (Worksheet Change Event) to do the expected task rather than wasting your time with Validation.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  8. #8
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    252

    Re: Dynamic Data Validation

    Hi Sandy,

    Thanks for your feedback. I followed all the steps you outlined but it did not change the results from my previous Data Validation List. I am wondering if there is a step I missed. Just to be clear, my goal is to be able to Change a name in the Client-Sales Validation List and have it automatically change the name in the cell that was referring to it without me having to reselect it from the drop down list.

    Attached is an updated file with the changes I made per your recommendation. As you will see, I changed the name in cell A4 of the Client-Sales Validation List to "University of Southern Florida2" but it did not automatically update the contents in cell B8 of the Sales Rep Forecast Input tab. Of course I am able to select the updated name from the drop down list, just as I was able to do before this change, but I am trying to avoid that step as it is too laborious. Moreover, my task will involve copying and pasting a variety of new names into the other Validation lists like this, which need to update those cell results so I do not have to go back in and manually reselect each one.

    I hope that makes further sense.

    Thank you in advance.
    Attached Files Attached Files

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Dynamic Data Validation

    I misunderstood before, sorry
    Like above ppl said: it is impossible without VBA.

  10. #10
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    252

    Re: Dynamic Data Validation

    Thank you. Would you know how to create a VBA that solves this?

  11. #11
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    252

    Re: Dynamic Data Validation

    Thank you. Do you know how to create a VBA to do this task?

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Dynamic Data Validation

    Maybe it will help you:
    http://stackoverflow.com/a/30166524
    but I am not an VBA expert so I can't help you more with this problem
    regards

    but in my humble opinion, what you want to do is to deny the idea of a DropDownList so that's why developers do not let do that.
    Last edited by sandy666; 10-24-2015 at 03:31 PM.

  13. #13
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    252

    Re: Dynamic Data Validation

    Got it. Thank you.

+ 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. If statement based data validation? Dynamic data validation?
    By bjohnsonac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2014, 02:12 PM
  2. [SOLVED] Dynamic data validation
    By phalcon45 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-07-2014, 02:33 AM
  3. Dynamic Data Validation in VBA
    By dchanson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2012, 04:45 PM
  4. [SOLVED] Dynamic Data Validation
    By marreco in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-22-2012, 07:57 PM
  5. Dynamic data validation
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-17-2011, 12:54 PM
  6. Dynamic Data Validation
    By SystemsAccountant in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-24-2009, 12:01 PM
  7. Dynamic Data Validation
    By SystemsAccountant in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-20-2008, 08:11 AM

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