+ Reply to Thread
Results 1 to 3 of 3

Data Validation for IF('15 cells above'<>abc,TableA,"")

  1. #1
    Registered User
    Join Date
    11-17-2014
    Location
    Hudson Falls, New York
    MS-Off Ver
    Home and Student 2010
    Posts
    15

    Data Validation for IF('15 cells above'<>abc,TableA,"")

    Need the '15 cells above' reference function I was looking at OFFSET, or INDEX maybe? But not sure.

    There are 100 drop-downs going from E17 -> E116. Each one has the potential to draw from one of three tables, depending on how far down the list they are and what is chosen for the drop-downs above. drop-downs up to 4, 8, or 16 cells above, can influence whether or not you can choose a new item in the current drop-down.

    Example I've been using in data validation that I'd like to clean up and make more crisp:

    =IF(AND(C25="",C26="",C27="",C28="",B17<>"714-16",B17<>"715-16",B21<>"714-8",B21<>"715-8",B21<>1158,B21<>"714-16",B21<>"715-16"),Block3_4,"")

    This line is from a separate section that also is affected by the adjacent column. The B cells are merged in groups of 4's as the items that go in them take up four spaces when used. The B's portion is what I'd be using except instead of looking at one cell, I want it to look at a range of cells, since the section these will go into are not merged. 715's are zone expanders on a fire alarm panel. they come in 4, 8, and 16 zone sizes. 714's are security expanders for the same system and also come in 4, 8, and 16 zone sizes. They can be addressed to any address in the 100 zone expansion bay, and when assigned, eat the 3, 7, or 15 following zone addresses so nothing else can be assigned to those consequent zones. The zone list is vertical, with zone descriptions in the next column. If a 714 is assigned to E22, for example, E23, E24, and E25 would not be able to be assigned as anything because they are the 2nd,3rd, and 4th zone on that expander. I need the data validation for each drop-down to check the previous 15 cells for 714-16 and 715-16, and the previous 7 cells for 714-8, or 715-8, or 1158 (wireless zone expander...) and so on... I could probably sit down and write out a REALLY long IF(AND(),.,.) formula, except that half of the users who will be using this spreadsheet are using openoffice or some other free spreadsheet program. So VBA, and more than a single nested IF are not going to work... Any ideas?

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Data Validation for IF('15 cells above'<>abc,TableA,"")

    It would be very helpful if you could upload a sample workbook, showing the data you're working with and an example of desired results, on separate tabs if necessary. You can upload a workbook by clicking Go Advanced, and then Manage Attachments. Click Browse to locate your file, then Open, and finally click Upload.

  3. #3
    Registered User
    Join Date
    11-17-2014
    Location
    Hudson Falls, New York
    MS-Off Ver
    Home and Student 2010
    Posts
    15
    Quote Originally Posted by Melvosh View Post
    It would be very helpful if you could upload a sample workbook, showing the data you're working with and an example of desired results, on separate tabs if necessary. You can upload a workbook by clicking Go Advanced, and then Manage Attachments. Click Browse to locate your file, then Open, and finally click Upload.
    I will work on that. 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. [SOLVED] Data validation "list" - "source" has too many characters
    By bee88 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-21-2015, 04:28 PM
  2. Data Validation - how to ignore cells with "0" or "-"
    By dgibney in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-29-2015, 08:16 PM
  3. DATA VALIDATION ALLOW TEXT "DROP" and "check"
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-06-2014, 12:52 AM
  4. Replies: 1
    Last Post: 10-23-2013, 05:20 PM
  5. [SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2013, 04:49 AM
  6. [SOLVED] Manually fill up 1 or (some) cells with just "BFW"or"BF" in data validation
    By robke1960 in forum Non English Excel
    Replies: 2
    Last Post: 12-11-2012, 11:31 AM
  7. [SOLVED] How can "data validation" be enforced in calculated cells
    By NirA in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-24-2006, 02:15 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