Home > Cannot Be > This Type Of Reference Cannot Be Used In A Data Validation Formula

This Type Of Reference Cannot Be Used In A Data Validation Formula


Better to spend an extra 10 seconds to add flexibility and maintainability. Matt Julien's option may be cleaner in terms of adding future values, but it's far less elegant if your name range is needed in other parts of your workbook. Its name is "Ingredients." But Data validation won't allow "Ingredients" as a source, because it says "The List Souce must me a delimited list, or a reference to single row or In the course of showing her, I realized that, therein, was one of those handy little tips worth sharing. http://kshelper.com/cannot-be/outlook-data-file-cannot-be-accessed-2013.html

All rights reserved. Now people can only select from your major list if they are using one of the cells specified in step 6, and from the appropriate dependent lists if they choose one Not today. Learn more about Allen...

This Type Of Reference Cannot Be Used In A Data Validation Formula

Using the same household chores scenario, I would accomplish the same end result, sans limitation, as follows: Add a new worksheet (I usually name it something like "Lookups" and then hide the Is there another way to remove the data validation from a cell? I recommend reading that one rather than this one. How can I retain the original form?

The Data Validation dialog box. Anyways, please share your method. When I get to step 11, I receive an error that says "A named range you specified cannot be found." Our Company Sharon Parq Associates, Inc. Thanks for the article - good advice mate.

If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. This Type Of Reference Cannot Be Used In A Conditional Formatting Formula Share it with others Like this thread? The macro itself is Public Function Rex(ByVal vsStringIn As String, ByVal vsPattern As String) As Boolean Dim objRegEx As Object Set objRegEx = CreateObject("VBscript.regexp") objRegEx.Global = True objRegEx.Pattern = vsPattern Rex http://excelribbon.tips.net/T010545_Creating_Dependent_Drop-Lists.html Press Ctrl-G to sheo the Immediate Window, if it is not already visible In the Immediate Window, type: Please Login or Register to view this content.

BUT, on a hunch, I then tried putting the table name inside of an INDIRECT function, and it did! I am using MS Excel 2010. Author Bio Allen Wyatt With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. You can find a version of this tip for the older menu interface of Excel here: Creating Dependent Drop-Lists.

This Type Of Reference Cannot Be Used In A Conditional Formatting Formula

FaultWire View Public Profile Find More Posts by FaultWire « Previous Thread | Next Thread » Thread Tools Show Printable Version Email this Page Display Modes Linear Mode Switch to Hybrid see it here Tim Wilson Hi Edie, It may be that Excel for Mac is causing the issue -- I've definitely run into issues with the Mac version of Excel when it comes to This Type Of Reference Cannot Be Used In A Data Validation Formula So what I usually do is: - set a tab for all the dropdown values - in column A, for instance, give the name for the list, like tasks - in You Cannot Use References To Other Worksheets Or Workbooks For Data Validation Criteria I'll follow up with you via email to get an example document and see what we can do.

What is the definition of "rare language"? his comment is here However, one of the columns is solely for labeling purposes. There are actually a number of different ways you can accomplish this task, ranging from simple formulas to complex macros. Adding to the bottom of your list normally (with standard table settings) adjust your table size, and thus your named range. This Type Of Reference Cannot Be Used In A Data Validation Formula Excel 2013

Nope. Subscribed! Here is the FAQ for this forum. + Reply to Thread Results 1 to 2 of 2 Data Validation: Named Range You Specified Cannot be Found Thread Tools Show Printable Version this contact form I'm leaving my original approach here to provide a "multiple ways to skin a cat" demonstration…but I expect I'll be using the approach described in the comment.] This is simply a

I might. When done, the rest of the row will automatically be filled in with data pertinent to the selection. Elizabeth Much easier to type the wanted list, set it up as a table and then name the range.

After entering the information into the cells (A2:A10 for example) highlight these cells and change the name to something relevant (like dependants) this creates the "named range" then use this name

Your cache administrator is webmaster. Tim Wilson I really stopped using this method and use tables instead, which auto-expand (see the link at the top of the post). When I select the list, all text and numbers turns to normal. Unless it is a hugely complicated list with a lot of different formats.

I tried the "Table" functionality, also something I am not familiar with, and got nothing, since I can't use the table name in the Data Validation- even when I tried "Indirect" When you add a value at the bottom of the table the named range automatically stretches to include to the new value! Excel will prevent input that is not in the (dynamic) named range. navigate here Thanks for a nice article BTW!

Bryan26 Feb 2014, 11:44 Nitin, do you you want to see a whole list displayed or just one matching record? Helpful Links ExcelTips FAQ ExcelTips Resources Ask an Excel Question Make a Comment Free Business Forms Free Calendars Tips.Net > Excel Home > Tools > Data Validation > Creating This tip (10545) applies to Microsoft Excel 2007 and 2010. The named range expands to include a range of values that presumably is explicitly entered to be used as validation.

Tim Wilson That's a good point. Guessing game - Is it a 40? Edie Hello all- Quick question… I am working in a Workbook with a dynamically named range and am looking to use that named range in another sheet within the workbook. To view the full version with more information, formatting and images, please click here.

If you have multiple records attached to each name/ID in the drop down (like a customer order table -- each customer could have multiple orders) it is more complicated but the