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.

View 9 Replies


ADVERTISEMENT

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.

View 3 Replies View Related

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

View 7 Replies View Related

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

View 4 Replies View Related

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.

View 3 Replies View Related

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.

View 4 Replies View Related

Using INDIRECT Function To Create Custom Validation

Jun 28, 2013

I have a worksheet that uses the INDIRECT function as part of cell validation to generate a custom 'name' range, this name then references a bunch of sheets that contain the actual range where the values for the validation list are stored. For example: ValidationExample.xlsx

Name = Color
Values = Blue, Red, Green

Name = Shape
Values = Square, Circle, Triangle

So what this allows me to do is in the first cell, I can define two options such as Color and Shape. Once one of these is selected, the next cell will have a formula for the validation as "=INDIRECT(A1)" then I will define two named ranges called 'Color' and 'Shape'. This will then show me the list of items in the cell based on previous selection. An example of this is attached to this posting above.

When the formula is written into the validation, an error message is generated saying that the formula will generate an error. Even with this message, the method works effectively to provide a blank list if nothing in the first cell is selected, or a list dependent on the selection of the first cell.

In this case, what I want to do is generate a macro that will populate the validation for a cell when new records are added. This won't always copy down from previous cells because the way I add records is through use of a macro and I generally find using the format painter in vba to be a fairly sloppy way of getting formats from other cells. So I go through a series of validation additions to each cell in the worksheet to get this validation created (I have no written this into the workbook attached).

VB:
Dim i As Long
Dim lastRow As Long
Dim sht As worksheet

[Code] .....

As I read in previous posts, Formula1 cannot contain an actual formula, only names and lists of items. Since each of the cells will have a changing reference, thus the INDIRECT function would need to change to reflect this, The 'Secondary' name consists of the following:

"=INDIRECT(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN()), 0,-1)))"

Which of course does not work due to the error when using INDIRECT in the first place (though the Address() function appropriately finds the cell with the validation in it's appropriate address). I can think of other ways to do this, such as having validation lists change in with VBA on a SelectionChange or Change event, but that's a lot of code that is subject to change when the named ranges have changes to them (which is fairly frequent). Is there any way to get the .Validation.Add method to ignore errors in the name supplied to it.

For reference, my code is this:

VB:
Private Sub cmdRefreshValidation_Click()
'Re-enters validation parameters to all cells to allow selections
Dim i As Long
Dim lastRow As Long
Dim sht As Worksheet

[Code] .....

View 1 Replies View Related

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.

View 3 Replies View Related

IF Function With Data Validation List?

Aug 12, 2012

Is it possible to set up a cell to either return a vlookup result or allow a specific data validation list? For example, if precedent cell is blank, then I want to perform a vlookup and return result, but if not, then only allow a choice from an unrelated list.

View 5 Replies View Related

Function Not Working In Data Validation List

Jul 23, 2014

I need to compare 2 cell with 1 specification reference.But the function can't give return value as per required.Both 2 input cells using Data Validation List.

Please refer attachment for some examples : matching.xlsx‎

View 2 Replies View Related

Data Validation List - OFFSET Function

Apr 20, 2012

I have the following formula in a Data Validation List:

=OFFSET(B8,MATCH(J2,
(CHOOSE(B7,D1Array,D2Array,D3array,D4Array,D5Array,D6Array)),0),1,COUNTIF(CHOOSE(B7,D1Array,D2Array, D3array,D4Array,D5Array,D6Array),J2),1)

In B8 it is the formula =VLOOKUP(B7,LookupTable,2,0). and the lookup array is this 1$H$112$J$113$L$114$N$115$P$116$R$11

So the value returned in B8 will be a the start reference for the OFFSET function. This works but it retuns blank values and not the values in the arrays.

View 3 Replies View Related

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

View 1 Replies View Related

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?

View 3 Replies View Related

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?

View 14 Replies View Related

Obtaining List Of Worksheet Names For INDIRECT Function

Jun 11, 2008

I am familiar with the use of the INDIRECT function to retrieve data from different sheets in a workbook. However, is there a simple way of obtaining a list of all sheet names in a workbook (I have about 50) rather than typing them into the sheet individually?

I can see them in the workbook properties but can't copy and paste them.

View 9 Replies View Related

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.

View 2 Replies View Related

Dynamic Named Ranges And Indirect Data Validation INCOMPATIBLE?

Sep 25, 2009

I can use a dynamic named range and a direct reference to the name and the DV works.

I can use a standard named range (not dynamic) and an INDIRECT() reference to a cell with the name of the range in it and the DV works.

But if I try to use a dynamic named range and an indirect reference to a cell with the name of the range in it, the DV fails.

Any workarounds out there? This project includes a LOT of DV lists, getting them to self-maintain is important if I can do it.

View 12 Replies View Related

Getting Data Using Indirect Function?

Jan 18, 2014

I have some data on a sheet and I want it on other sheet without using lookup function. Only "Indirect function need to be used". find attached the excel sheet.

View 1 Replies View Related

INDIRECT FUNCTION - Make Data Stay Permenantly

Dec 9, 2009

I have approx 500 workbooks which contain custoemr details and I need to extract certain info (postcode, name, contact, phone number etc) and place it all on 1 spreadsheet. I was originally going to source data from unopen workbooks, but have realized I only need to do this task once, and I can maintain it from then on...


I have created a spreadsheet (attached) which uses INDIRECT formulas to retrieve the data. When I type in the file name in column A, Columns B to K fill themselves in. What I want to know is:

Once I open say, 10 Workbooks, TYPE the name in manually and all the other cells have filled themselves, I want to somehow KEEP the data there, rather than it return a #REF error when I close the workbooks to open the next batch.

Also, if you look at my workbook, to the far right is a range of data which I use in my INDIRECT formulas. Once I have filled all the cells with the relevant data, I want to be able to Filter my spreadsheet by Date Ascending. BUT I cant seem to do it because it includes my INDIRECT range in the filter and mess's up the formulas then I have tried Locking & protecting workbook, with Autofilter & select cells etc TICKED for users to use, but it still tells me to unlock workbook when I try to filter.

View 5 Replies View Related

Data Validation Used To Create Separate Data Validation List

Feb 15, 2014

I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.

i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list

First Name
Surname

Paul
Smith

Paul
Jones

Tony
Phillips

View 1 Replies View Related

Lookup Data From Multiple Worksheet Using Indirect Address And Match Function

Sep 28, 2011

determining the correct formula to lookup various details from multiple worksheet:

Data as follows:
Sheet1: input
AB1MonthSales2AUG55003MAY4000

Sheet2: Data

AB1MonthSales2JAN20003FEB25004MAR30005APR35006MAY40007JUN45008JUL50009AUG550010SEP600011OCT650012NOV700013DEC7500

b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))

i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3

and each table has unsorted details compared to others sheets (Data vs Data 2)

View 9 Replies View Related

Hyperlink Function Inside Of A Validation List?

Feb 22, 2007

Is it possible to use the HYPERLINK function inside of a validation list?

Presently I have a dynamically named range on another worksheet that includes a hyperlinked term. When I attempt to create a validation list using this named range the term appears in the list without the hyperlink.

View 7 Replies View Related

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.

View 10 Replies View Related

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.

View 4 Replies View Related

How To Get Unique List From A Column To Data Validation Dropdown List

Oct 28, 2011

I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?

View 5 Replies View Related

Excel 2007 :: Data Validation List Depend On Another DV List

Apr 29, 2012

I have a List of Different Fruits in Cells A1 to A5

Apple
Banana
Orange
Strawberry
Cherry

And I use data validation list in 5 different cells from Cells C1 to C5 then in every cell the list will show all the fruits,

But I want that if I select Any Fruit in cell C1 that should not be included in the remaining 4 cells, and the fruits selected in Cells C1 and Cell C2 should not be included in the remaining 3 cells and so on....

I Used the formula

=IF(C1=A1,OFFSET(A2,,,COUNTA($A$2:$A$5),1),0)

But this works fine if I select Apple in the Cell C1, then the List of C2 Shows all Fruits other than Apple, But if in Cell C1 I select any fruit other than Apple it does not work... (Using Excel2007 & Win XP)

View 4 Replies View Related

Setting Widths List Box In Data Validation List

May 12, 2004

dear....can i set width in list box data validation (width in cell i set short).thanks

View 9 Replies View Related

Force Choice From List: Data Validation List

Oct 18, 2006

is there a way to force so that a cell value can be only of what a validation droplist offers, hence users can not enter their own values and have to use the droplist for cell content selection.

View 2 Replies View Related

Data Validation List, Disallow If Not In List

Jul 16, 2009

I am using Data Validation on some fields to create a drop down list from a named range! These fields however allow you to enter values that are not in the list.

Is there a way to make the cell have to be an entry from the data validation list?

View 6 Replies View Related

Converting Data Validation Function To A Vba Script

Dec 5, 2009

I have a data validation on a cluster of Cells that has a name.range = "match". On clicking on a Cell in Row 61 belonging to range = "match" i get a Data Validation|Custom formula(Alt+A+V+V) "=J61>=0"

now how can i convert this function of Data Validation to a Vba script.

To be Exact what i need is....
If a value is entered in cell belonging to range.name = "match"
then check the value in "corresponding row" of the column J.
If Value is greater than J
then stop vba script and show message box.

If value is not greater than J then run desird Code.
I hope the following picture might clear things even more of what im looking for..

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved