+ Reply to Thread
Results 1 to 6 of 6

Data validation and If statement

  1. #1
    Registered User
    Join Date
    04-25-2013
    Location
    Wyoming
    MS-Off Ver
    Excel 2010
    Posts
    6

    Data validation and If statement

    Hello Everyone,
    I am looking to use data validation with an IF Statement in it. I have seen several other post similar but not this. My company keeps a list of higher end customers, the lower end customers are also recorded but not in a manner that is easy to access. At the end of the month I need to create a report that of all (Higher and Lower) services installed for the month. For the Higher End customers my report can use index and match to auto populate all of the fields, for the lower end I have to fill it out by hand. I have a field that had Data Validation to specify Higher end or Lower. What I am looking to do is when Higher end is selected in that cell, the Customer Field turns in to a drop down list from the Higher end tables. When Lower end is selected, the Customer field allows any value.

    This is what I think it should look like in the Data Validation List Source

    =if(B5="Higher End",'Higher End Services'!$AO:$AO,ANY)

    The part I can't seem to figure is the ANY part. Also "Higher End Services" is a table and the Customers are in Column AO.
    Thank you for any help you can provide.

  2. #2
    Forum Contributor
    Join Date
    02-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    272

    Re: Data validation and If statement

    Firstly Give a name to AO:AO as "High_End" & then in validation into list put following function:

    Please Login or Register  to view this content.
    Assuming your data in A2 & also use High_End instead of High End
    Please consider adding a * if I helped

  3. #3
    Registered User
    Join Date
    04-25-2013
    Location
    Wyoming
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Data validation and If statement

    Correct me if I am wrong, but that still only defines the High End list. The trouble I am having is related to the ANY part in my equation. I don't have a list for Lower End, if it is Lower End I need to enter it manually.

  4. #4
    Forum Contributor
    Join Date
    02-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    272

    Re: Data validation and If statement

    No worries, if High_End is not selected it'll allow you to enter any value.

  5. #5
    Registered User
    Join Date
    04-25-2013
    Location
    Wyoming
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Data validation and If statement

    I did that and it does work for High_End. However when I select Low End it gives me a data validation error when I type something in.

  6. #6
    Forum Contributor
    Join Date
    02-04-2012
    Location
    Belgium
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Data validation and If statement

    you are looking like this

    JP
    Attached Files Attached Files

+ 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. DATA Validation and IF statement
    By NCAA in forum Excel General
    Replies: 3
    Last Post: 03-29-2012, 04:01 AM
  2. Data validation in if statement
    By mahoo in forum Excel General
    Replies: 3
    Last Post: 02-15-2011, 04:06 PM
  3. Data validation IF statement
    By christopherp in forum Excel General
    Replies: 4
    Last Post: 11-12-2009, 11:27 AM
  4. [SOLVED] How do I use a conditional (IF) statement in Data Validation?
    By Debra Dalgleish in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 02:05 PM
  5. using the if statement with data validation
    By GreenMonster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2005, 04:06 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