+ Reply to Thread
Results 1 to 9 of 9

Data validation list updating

  1. #1
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Data validation list updating

    Hi,

    I have on going data in AH column.The data will be go on adding in this column.What I want is that I want to get dropdown in list the unique data and if any new data is not there ,then it will allow me to add manually and in subsequent row it will again update that new data too.

    Suppose in AH column, from AH2:AH6 there is 3 unique data.I have created a data validation list in AH7.It is currently showing 3 unique data including blank spaces.Now in AH7,I want to add "Janet" this should be allowed to do entry and it must update the list in AH8 cell.

    Is this possible ?If so,kindly help me in this.
    Attached Files Attached Files

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Data validation list updating

    You will have need to make some helper column

    try

    Aj=IF(ISTEXT(AH2),MAX($AJ$1:AJ1)+1,0) and drag down

    AK=IFERROR(INDEX($AH$2:$AH$6,MATCH(ROWS($AK$2:AK2),$AJ$2:$AJ$6,0)),"") and drag down!!!

    Now Press ALT+M-->> N you will get a window Name Manager click on new then you will get another window New Name Put name whatever you want but note without any space then below formula in refers to

    =OFFSET(AK2,,,COUNTIFS(AK:AK,"?*"))

    Then chick ok.

    Now go to that cell where you want drop down. Press ALT-->> A-->V-->V and and select allow with List then in source field you will have to press F3 key and select the name manager range and then ok.

    It will help you/


    Check the attached file.
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: Data validation list updating

    Its not working in AH column when I type any data and data validation in subsequent cell of AH column.

  4. #4
    Registered User
    Join Date
    05-25-2016
    Location
    United States, Texas
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Re: Data validation list updating

    Try this...

    List of all names on Sheet2:

    Data Range
    A
    1
    All Names
    2
    Name1
    3
    Name2
    4
    Name3
    5
    Name4
    6
    7
    8
    9
    10


    New names will be added at the bottom of the list.

    Data validation list to select a name is on Sheet1 in cell A1.

    As the source for that list use:

    =OFFSET(Sheet2!$A$2,,,COUNTA(Sheet2!$A$2:$A$100))

    Adjust the end of range A100 as needed to allow for future name addition.[/QUOTE]

  5. #5
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: Data validation list updating

    No,my source column is same and destination of data validation is also in same column.If any new name will be there in subsequent cell,then it will be typed in that cell of Column AH and it must allow i.e if a new name "Ravi" appears in AH6 ,then it must allow to type in AH6 but in AH7 data validation,the whole list from AH2:AH6 must be updated and likewise.
    Last edited by paradise2sr; 05-27-2016 at 11:28 PM.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Data validation list updating

    is it ok if we create a helper column
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: Data validation list updating

    yeah,but plz note that I will be having dropdown or datavalidation in AH column as I will be selecting data from that column only.If any new data is not found in the list,I will be typing manually in the cell in AH column but in very next row (cell) the data validation list must be update newly data entered in previous row(cell).

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Data validation list updating

    Created a Helper Columns in AM & AN
    Created name manager and applied data validation see attached file
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: Data validation list updating

    Hi instead of creating Helper column in AM & AN,can u kindly do it in separate sheet.And pls explain me your formula in detail stepwise AH & AN ,this you can do in excel sheet.

+ 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. Updating cells based on selection from drop down list data validation
    By excelstun in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-18-2016, 05:11 PM
  2. [SOLVED] Updating cells based on selection from drop down list data validation
    By jingles9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2013, 02:57 PM
  3. Updating cells when a different name in a Data Validation list is selected
    By movali01 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-14-2012, 10:02 AM
  4. Creating a self updating Data Validation List
    By TMS in forum Tips and Tutorials
    Replies: 14
    Last Post: 11-01-2012, 10:02 AM
  5. Replies: 0
    Last Post: 02-23-2012, 10:57 AM
  6. Updating cells based on data validation list
    By benlawton in forum Excel General
    Replies: 10
    Last Post: 05-15-2009, 07:27 PM
  7. Replies: 2
    Last Post: 04-14-2009, 02:40 PM
  8. Updating a Validation List
    By Dmorri254 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2006, 09:00 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