# Data Validation Indirect Where Multiple Values Show Specific List?

Feb 7, 2014

I have a data validation list where more than one value should show the same list. Instead of make multiple range, I want to make it so if any of the values are in a cell the data validation list will show the list that goes with those values.

## Data Validation List - INDIRECT Function

Mar 28, 2014

I am using a data-validation-list with =INDIRECT(B7) as the source. It works as expected except for after the file has been closed and reopened. When it is reopened, the source is changed to =INDIRECT(#REF!).

I have tried =INDIRECT('Worksheetname'!B7) but same result.

## Develop An Indirect Indirect Validation Drop Down List

Aug 18, 2009

I am trying to develop an Indirect Indirect Validation drop down list. Example, Building - Floor - Room, i.e. Select Building from a Validation drop down list. Then based upon the Building selected, select only the Floors applicable to the Building Selected. I am able to achieve this via an Indirect Validation drop down. However, when I attempt to then select the Rooms applicable to the Floor of the Building I selected, I can not produce an Indirect Validation off a previous Indirect Validation.

In the attachment, I have used Plant - Location - Room. I have name ranged the selections, and have used Validations Lists for Plant, and Indirect Validations for Location. The error occurs where I attempt to do an Indirect Validation for Room.

## Excel VLOOKUP Multiple Values From Data Validation List

Dec 12, 2011

I've found a nice looking formula for looking up multiple values from a Data Validation List which you can find here.

Unfortunately I dont know why im getting a #REF! error for one of my spreadsheets. Here is my Formula {=SUM(VLOOKUP(A2,D:D,{6,7,8},0))}

A2 is the Data Validation drop down list that has most of the Values (Letters & Numbers) I want to look up. D:D is where it will find the Values (Letters & Numbers) and {6,7,8} is the currency I want added up and displayed.

I've tried this formula on other spreadsheets with success, but no success with the spreadsheet im working on currently.

## Data Validation List Automatically Show The First Item In The List Rather Than Showing Blank

Jun 20, 2008

I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.

## Data Validation List Can We Show Colors

Dec 9, 2008

in a data validation list can we show colors,assume the following list as a data validation list

Item #
Orange =50
Apple =60
Banana=56
Mango = 40

in this list i want to give colors (as shown) to these two items

## Conditional Data Validation: Show All Values

Jan 6, 2009

I need someone to go through them assigning a building number, level and location to each of them. I am using drop down menus, the first choice is building and has the following options:

B1
B2
B3
B4A
B4B
B5
B7

Depending on the option selected there maybe different locations, i.e. B1 has the following available locations:

1-4
4-16
MER
ALL
KIT

Whereas B7 only has: ALL. The third column, level, operates in a similar way.

## Dynamic Validation List And Indirect Function

Sep 18, 2006

I have a dynamic validation list (see attached) which will not accept the indirect function as a formula in the listbox.

Using the named range on its own works fine, but using the indirect generates an error.

## Dependent Validation List Errors With Indirect & Vlookup

Dec 31, 2008

troubleshooting dependent validation list formula. I found the Ozgrid instructions for the Dependent Validation Lists to be verfy informative and I downloaded the "MatchingLists.xls" to use as a starting point.

My challenge is this: The MatchingLists.xls example does not take into account that there may be MULTIPLE dependent lists that have spaces, numbers and other unique characters in the list's source celles. Therefore, I have decided to reference a NameLookup (named range) that has an abbrevieated name for each each item (without numbers & spaces) I am nesting VLOOKUP within the INDIRECT formula to achieve the results I need - but for some reason it fails occasionally and I do not know how to troubleshoot!

Data Validation Source =INDIRECT(VLOOKUP(SUBSTITUTE(E5," ","_"),NameLookup,2,0))

Excel's Data Validation simply returns the message "The source currently evaluates to an error". This is frustrating becuase it is not consistent. It works with some lists and not with others. How do I troubleshoot???

My worksheet is attached for your reference. The source data for the dependent lists is on the Lists sheet. The dependent drop-downs are in columns E-H on the drop-down sheet. The formula that I am having trouble with is in "Subcategory" (column F). For some reason, I cannot choose category "26_Heat_Exchangers" to populate the subcategories. There are a few other categories that this formula breaks on and I don't know why. Please tell me this is not some crazy limitation with Named Ranges.

## Named List: Data Validation To Restrict The User To Only Selecting Values In A List

Jun 15, 2007

I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.

Insert > Name > Create
Name: Fruit

Refers to:
banana,apple,orange

When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.

## Data Validation Using Indirect

Feb 11, 2014

I've attached a sample file of what I am trying to do.

In row 2 I have data validation in the form of lists. Below row 2 is the data.

Cell B2 has an indirect expression referring to cell A2. In other words, B2 is dependent on A2 (cascading)

What I would like to happen is when a new selection is made in cell A2, B2 populates with "Choose platform", instead of keeping the old information. The drop down populates with the new data but cell B2 still displays the old data.

Book2.xlsx

## Data Validation List Depending On Other Values In Row?

Jun 25, 2014

Example:

Sheet: Users
Column A has "Yes and No"
Column B has "Names"

ex.
A B
Yes Peter Johnson
No Patrick Andersen
Yes John Smith

Now i would like to create a list in sheet [Employee] where one the users with Yes in column A is shown.
In this ex.

Peter Johnson
John Smith

No need to show yes or no.

## Why Values Not In Data Validation List Being Accepted

Dec 14, 2011

I have several data validation lists that are driven by named ranges. These ranges include blank cells in order to allow for expansion of the list easily - without updating the named range itself.

Seems like because of these blanks rows...excel is accepting any value. The list is visible and can be used, but the user can also enter in a value not on the list. (when I remove the blank rows from the range, the validation works properly)

## Data Validation :: Display A List Of Values

Jun 4, 2006

data validation to display a list of values. The problem am facing is .. this list has a lot of names & it becomes difficult to scroll through when i need to search for a particular name.. Is it possible that when I press a key on my keyboard, the name starting with that letter is highlighted in the drop down list.. This is not happening automatically using data validation... Is there a work aorund for this?

## Data Validation Which Allows To Select Values From A List

Jun 8, 2006

I have data validation in cells A1:A50 which allows to select values from a list. There are about 50 items in the list that can be selected. Is it possible to have the user select an item only once i.e., if an attempt is made to select an item thats already selected it show an error message.

## Data Validation With Indirect For More Than 1 Entry

Aug 13, 2014

I am trying to arrange a dependent drop-down list and have the basic listing working. My problem however is that when i pull data from sheet "Mon" for column C using indirect it works but when I try to do the same thing for column D i get the same data as column C. How do i get indirect (or offset if it works better) to allow me to reference the alternate data 1 column over?

## Indirect Validation - Using Data In Second Worksheet

Jan 16, 2014

I have a competition entry form for an association I belong to that I'd like to add two drop down menus to so that entrants can select their club name and associated team names for that club.

In a worksheet behind the form I have a list of clubs in column "B" ("A" has the ID number in it as the table is from Access - don't ask!!) and the names of teams associated with that club going across the sheet beside the club name in individual Name Ranges. (Up to 30 Team names in Dynamic lists

=OFFSET(TeamsTest!\$C\$2,0,0,COUNTA(TeamsTest!\$C\$3+TeamsTest!\$C\$3:\$AZ\$3),1) )

On the form I have managed to make the first drop down so the club can select its name from a list by using Data Validation and referring to the Range Name (ClubLink - =OFFSET(TeamsTest!\$B\$2,0,0,COUNTA(TeamsTest!\$B\$2:\$B\$200),1)).

I have tried using an INDIRECT command to create a second drop down which will display only the team names associated with the Club selected in the first drop down, but can't get it to do anything - no drop down appears when you click on the drop down arrow at the side of the cell

The data Validation for the Team Name drop down is =INDIRECT(SUBSTITUTE(\$C\$12," ","")) where C12 is the Club Name cell.

Why I can't get the Team Name cell to show the names listed beside the Club selected in C12?

## Data Validation - Indirect Function

Jun 9, 2009

i know it is possible for a selection in a drop down box to determine another field using the INDIRECT function in validation

eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
2nd drop down box (if chosen rugby) - displays list of rugbyteams
2nd drop down box (if chosen cricket) - displays list of cricket teams

but is it possible for the 1st drop down box to determine what is available in a range of other drop downs?

eg - 1st drop down box - Football, Rugby, Cricket

2nd drop down box (if chosen football) - displays list of football teams
3rd drop down box (if chosen Man Utd) - displays list of Man Utd players

## Data Validation List With Static And Dynamic Values

Apr 24, 2014

Is it possible to have a dependent validation drop box that under certain conditions automatically produces an actual value in the cell instead of a drop-down list to choose from? For example, if I choose “Holy Avenger” in the first drop box, and the value for that choice in the second drop box is always a “2”, can you get it to auto-populate in the cell occupied by that 2nd drop box (cell B5 in the attachment)? Or, are you restricted to using Lists only, meaning that you could create a List that only has the value “2” in it, and the user would be required to choose the “2” in the drop box?

## Dependent Data Validation - Indirect Function

Apr 10, 2014

See attached. I cannot get my indirect function to work in Cell K2 based on cell J2

Data for the Validation is in cells W1 to AC14

I want to do data validation in Column K based on Column J, but cannot get the indirect function to work to past into the data validation tool

I have named all my ranges.

AM Call Log.xlsm

## Data Validation INDIRECT & Relative References

Oct 6, 2006

I am setting up a spreadsheet for user data entry. I have one sheet set up as a template to enable users to copy the required data header cells to subsequent sheets and (the problem) - to different locations on the subsequent sheets. The template is using validated lists with the criteria drawn from the cell/list directly above the current list. For example, the cell in R11C2 is validated/refering to the range: =Campaign

The cell directly below this is validated/ filtered by: =Indirect(R11C2). This works great in the template, or any subsequent sheet in which the cells are all located in the same row/column. However, when the template is pasted in a higher row, the Indirect refers to R11C2 rather than referencing the cell directly above.

## Select Multiple Items From Data Validation List?

Feb 4, 2014

I have created a DAta Validation List and it only select one item. I want it to select as many items that is on the list but i can't get it to work for my main worksheet I am working on. I can't figure out why I can't get it to work for the work sheet "This Don't" but it works for the worksheet "This Works" why and how I can change it so both Reason (Column F and H can) select mutliple items on there. Data Sheet.xlsx

## Data Validation List With Multiple Named Ranges?

Mar 25, 2014

Can you create a Pull-down List that contains data from Multiple Ranges.

If I have a List of Names Running down column A (A2:A10) another List of Names Running Down B (B2:B25) and a Third down C (C1:C15) each of them named Ranges ("List1", "List2" & "List3"), can I create a Pull-down list in Cell A1 that would include the names from all three ranges?

## Multiple Named Ranges In Data Validation List

Feb 26, 2008

is it possible to refer to more then one named range in my validated list's source field? If not, how can i make more than 1 named range to be the source of my validated list.

## Data Validation Multiple Values - Without VB

May 29, 2013

I am trying to create a Data Validation drop down, that is based on contents of 2 different cells, without using VB. I tried doing this with an "if" formula, but it did not work.

Column F is a DV with a list for Area. I can use "=INDIRECT(F115)" to have separate ranges for column G, but I would like to use DV in column H, based on the values in F & G.

F
G
H
Area
City
Resource

[Code]....

## Two Cells Data Validation List Should Be Populated In Third Cell With Unique Values?

Apr 21, 2014

In cell H5 there is list of dates and in H7 there is codex, based on these two conditions in H11 a data validation list should be populated from the code_sheet using column B. The populated list should be unique entries, as the column B in code_sheet has duplicates.

## Multiple Dependencies For Data Validation Dropdown List In Excel - NO VBA

Feb 7, 2012

So, I need to figure out how to make the populated contents of a single dropdown box dependent upon the selected results in two other drop downs.

As there are ~35K lines in the sheet, creating Lists is impractical, and VBA is out due to client security settings.

My data sort order is: Region Name > Area # > Facility Name

I have a drop down to select the Region I want to work in.

The drop-down for Area # populates based on the selection from Region Name.

The hang-up is that the area numbers are 1-4 for each Region.

So, I can't simply populate another drop-down for Facility from the result from Area #, but it has to be based upon the results of both of the former results.

## Using Data Validation To Create List Dependent On Multiple Rows?

Jul 28, 2014

I have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)

for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked

## Select Multiple Items From Excel Data Validation List?

Jun 7, 2014

I watched a video on youtbe. And the women had three or four items in drop down box. She was using vba. When i emailed her to ask her how she did this, she kept telling me to go to her website and i was supose to figure it out on my own. I am trying to do a start up construction company. the link to her site is as follows Select Multiple Items from Excel Data Validation List - YouTube the idea of what i want to do, is how she clicks on a word and it follows in the coloumn to the right going down. This is what i need to know how to do. It is probably pretty elementary to many of you. But i build, i know how to read prints, to cut footprints for new homes etc. But when it comes to excel i am so green.

## Substituting Multiple Values For Data Validation?

Jan 3, 2013

trying to substitute out values that i am using in data validation. From the sentence:

Consulting (Logistics, IT, Management)

I would like to remove the spaces, the (, the ) and the commas so that it reads:

ConsultingLogisticsITManagement

So that i can use it for data validation.

In the data validation box i am trying the formula:

=INDIRECT(SUBSTITUTE(substitute(substitute(substitute(c2," ",""),"(",""),")",""),",","")

But i keep getting told i have an error.