+ Reply to Thread
Results 1 to 7 of 7

Data validation from main cell and sub cell

  1. #1
    Registered User
    Join Date
    04-28-2017
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    3

    Data validation from main cell and sub cell

    Hello : )

    I am trying to make a lot of data entry standardized in order to speed up entry and ensure consistency in the data. I am using excel 2016.

    I have a list of restaurant groups (McDonalds, Costa etc) in column A which I would like as a drop down list when I click on the cell. Once I select the relevant restaurant chain I would like the cell populated. Then if I move across the B column I would like it to only show the relevant venue addresses for that chain.

    I have tried to do this using Data validation but Im having trouble getting the B column to only show relevant restaurant chain addresses and not all restaurant addresses. I believe the data is best put into another sheet as the addresses may change from time to time.

    Any help would be great, THANK YOU
    Fluidnick

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Data validation from main cell and sub cell

    you can use INDIRECT to assign the relevant named range for this.

    So you will have a named range for McDonalds Say B2:B10 which contains only their addresses.

    Once you select McDonalds from the drop down the next cell for addresses will contain the list named McDonalds if you apply =INDIRECT(A1) N.B assumes that A1 contains the first drop down.

    If you upload a sample file I can help build this for you

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Data validation from main cell and sub cell

    Here's a more complex but versatile answer. I created two dynamic defined names, Restaurants and Venue.

    Restaurant is fairly simple
    =Sheet2!$C$2:INDEX(Sheet2!$C$2:$C$100, COUNTA(Sheet2!$C:$C)-1)

    Then to pull the proper venue addresses, I first position the active cell in B2 of sheet1 (next to the first restaurant you would choose (A2))

    The formula for Venue is then

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Modify your end cell (Z100) to properly represent the maximum number of restaurant names and venues you will have (i.e. M500 if you expect a max of 13 restaurants with up to 500 addresses)
    See attachment.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    04-28-2017
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    3

    Re: Data validation from main cell and sub cell

    Hi PFDave,

    Many thanks for the speedy response. If you dont mind sorting the formulas that would be great. I dont know what I am doing wrong and it looks so simple but just does not seem to work for me! Keeps saying 'source currently evaluates to an error'.
    This is the formula I put in cell b2 to create the drop down relevant to A2 client
    =INDIRECT(A6)+'VENUES SHEET'!$B$3:$B$80
    Thanks for your help : )
    Nick
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Data validation from main cell and sub cell

    Hi Fluidnick,

    Does this achieve what you are looking for?

    If so I can explain in a bit more detail, and we look at a dynamic range for the restaurants.

  6. #6
    Registered User
    Join Date
    04-28-2017
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    3

    Re: Data validation from main cell and sub cell

    Hi.
    Thanks works perfect thank you : )
    Ive copied the sheet across to my excel file and it works great. Many thanks for your help on this one!
    Nick

  7. #7
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Data validation from main cell and sub cell

    No worries Nick, glad I could help.

    If that takes care of the issue in this thread please do mark as solved and add any reputation which you see fit

+ 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] Post data from main sheet to different ones according to cell headers?
    By realrookie in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2016, 12:56 PM
  2. Data Validation Cell based on previous Data Validation Cell?
    By Dessesbo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-14-2015, 11:42 PM
  3. [SOLVED] Display data from sheets based on value in a cell on main sheet.. Help!
    By cpyter in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-29-2014, 02:20 PM
  4. Copy data from multiple sheets to one main based on cell value
    By jdjenterprises in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-28-2013, 10:13 PM
  5. [SOLVED] How do I copy data from main frame computer and keep data in cell
    By Doug in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-30-2006, 12:20 PM
  6. Replies: 2
    Last Post: 01-25-2005, 04:06 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