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
ADVERTISEMENT
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
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
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
View Related
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
Nov 12, 2013
I want to add some Data Validation for a cell and want it to validate that the entered data is in the format (DD/MM/YYYY HH:MM)
I guess I need to use a custom validation formula, however cant figure it out
View 1 Replies
View Related
Jun 26, 2014
I work for a Machine Shop in the Toledo area. We use a quotation sheet to quote our products to our customers. It has 3 cells that describe the Customer, the Contact person at that customer and finally that Contact's Email Address. I have a drop-down menu in each of these cells. The first, Customer, is based off a simple list of our customers. The Contact drop-down then uses the Indirect Function to search the worksheet for that Customer. The drop down is actually based off of a Range Name saved as that Customer's Name.
However the problem is many of my customers have & or , and since Excel does not allow those symbols or even spaces in a Range Name I have to use Substitute to be able to keep the spaces and the & or ,.
It is important that on the Quote Sheet the Name of the Customer is identical to the actual name of the company so I cant use AND instead of &.
Currently my Second drop down, Contacts, has a formula that looks like this {=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5,"_"," "), "3", "&"), "2",","))}.
My problem is that when I give the Range Name a Name , my drop down doesn't work and Excel tells me that the above formula results in an error.
View 6 Replies
View Related
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
Feb 24, 2014
I have four cells c1 thru c4. The SUM of these cells must equal 1. I want to display an error message "sum of cells not equal to 1.0" but only when data has been entered in all four cells.
For instance =SUM(C1:C4, "<>1") should display the error message. Is there a way to write the formula with an IF statement and still use data validation with error message box?
Assumptions:
-Will ignore blanks
-Error message "Sum of cells are not equal to 1.0" until values in all four cells have been entered, not after a value is entered in a cell. (validate expression after all four cells have values or blank)
Scenarios:
c1 = .25, c2 is blank, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .25, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .30, c3 = .25, c4 = .50 FALSE error message should display after value is entered in c4
etc
What I don't want is for the error message box to display after .25 is entered in c1 because the user is still in the process of adding values. Can this be done using Data Validation in Excel 2010?
View 2 Replies
View Related
Jan 15, 2013
How would I create a shortcut to automatically copy the contents of cell H1 to cell K1, in other word I would like to hit a shortcut key and have the contents of that cell pasted 3 cells to the right on the same line. I have to do this over and over again down the worksheet. I am working on taxes and want to copy values over to the expenses column as I find them.
I would also like to know if it is possible while having cell D1 highlighted I could have a shortcut created that would copy the value three cells over in H1 to cell K1. That would be the fastest, but I don't know if it is possible?
View 2 Replies
View Related
Mar 26, 2014
I've created a custom Function. I would like for there to be a Helper when a user is entering the Function arguments.
For example if I go into a cell and type in =VLOOKUP(
A popup with appear that shows...VLOOKUP(Lookup_Value,Table_Array,col_index_num,[range_lookup]).
Is it possible for me to build something like this in my Function?
View 4 Replies
View Related
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
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
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
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
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
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
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
May 11, 2009
I am trying to apply custom data validation for two cells. The first is data validation in cell B6. The only valid entries should be in mulitples of 100 (e.g. 100, 200, 300, .... etc). The second cell is B12. The only valid enteries should be any number above 100 and not = 100, 200, 300, .... etc). I tried using following formula in data validation for cell B6 but no luck. Couldn't figure out what to use for cell B12 =mod(B6,100)
View 5 Replies
View Related
Jul 5, 2007
I am trying to validate a textbox (txtCode) on my userform to accept the following type of entry: AB/123/07. The centre set of numbers will change & could also be four or five digits long. I have searched & found several threads but none that solve my problem.
View 8 Replies
View Related
Jan 25, 2008
I just need to find the MIN of P6 to Px. Where x is a number that is from another sheet. (Lets say B3)
I thought this would be it, but its still not working, gives me #REF! error
=MIN(INDIRECT("P6:"& '[filename.xls]Sheet 1'!$B$3))
View 9 Replies
View Related
Oct 17, 2013
I am trying to create a validation list, with its source data being filtered from another sheet of the same workbook. I also need to validate the drop list against some conditions in the source table.
Here is an example of the data am trying to create`(I want to create the droplist for the column Contract):
"Contract Definition" Sheet or Table:
-----------------------------------
Company Contract Service Report End date: Status
--------- --------- -------------- --------- ------
CompanyA ContractA1 Service 1 31/12/2014 Active
CompanyA ContractA2 Service 2 31/12/2014 Active
CompanyB ContractB1 Service 1 31/12/2014 Inactive
"Service Reports" Sheet or Table:
--------------------------------
Company Contract Service Report
--------- --------- ----------------
CompanyA ContractA1 Service 1
CompanyA ContractA1 Service 2
CompanyA ContractA2 Service 3
In the Service Reports Sheet, i want to create a list for each Contract, based on the specified Company and on the Status field, as defined in the Contract definition table.
View 8 Replies
View Related
Jan 17, 2014
I am trying to use Excel to create some XML output that is formatted specifically for an application that I have. The application expects to receive time data for two columns in hh:mm format, i.e. no seconds. The standard Excel data validation and also the XML time data type all expect hh:mm:ss.
In order to get around this, I have used string as the data type and I want to use data validation to check the user input conforms to hh:mm. I can't see how to write the formula directly, nor how to apply the validation formula to an entire column (as opposed to named cells or ranges), and although I could write a function using regex, I still couldn't use this because all the examples I can find relate to checking a specific named cell.
View 3 Replies
View Related
Apr 22, 2011
I totally understand how to make the combobox under form controls now but I am not having any success with the indirect function I was using as a list now that I have a combo box. I have attached the current form I am working on that just shows the list function still. How to convert this over to combo boxes with the indirect function?
I attached a second form with the feature I am asking about. It is just lacking the third list that I now have in place. (on the 1st attachment).
Attached Files
File Type: Corp MASTER (3).xlsx
File Type: Quote form (2).xlsx
View 8 Replies
View Related
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
May 8, 2014
I am having issues using the INDIRECT function to lookup data from a sheet with the same name as that appearing in a given cell. For example, in cell D27 i have the text "S1_358_810" (Not including quotations). I also have a sheet named "S1_358_810". My formula is as follows;
Formula:
[Code] .....
However this is returning #N/A. There is a list of numbers in sheet S1_358_810 in column N and from that I want the value in column Q (thus 17).
View 11 Replies
View Related
Dec 29, 2009
I have the following formula, which works; however I need to make it dynamic.
View 7 Replies
View Related
Feb 14, 2007
I have and Indirect function that works.... I need to modify it to include a cell address reference, but this requires the use of a Vlookup function to find the address ....
I have this formula: but it does not work
I'm not sure how to include the VLOOKUP function in my argument to
[/quote][/code]=INDIRECT(CELL("contents",B3)&"!&Address(VLOOKUP(B16,'1'!B:AE,match(RAY_ANALY!D2,'1'!B2:AE2,0),0)")
View 9 Replies
View Related
Feb 15, 2007
Always have problems getting my head round the syntax of the indirect function and am unable to find anything similar that's been asked.
I want to perform an operation on two numbers where the user selects which to use (add, subtract, multiply or divide) entered into another cell like this:
******** ******************** ************************************************************************>Microsoft Excel - 200701 - LCC.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC3C5=
ABCD14+5*2****3Normal*9*4****5Indirect*#REF!*Sheet3*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Mar 1, 2009
In Excel 2007, the following cell Q14 CSE formula accurately returns the row number of the first negative value in the column P array P14:P102.
{=MAX(ROW(INDIRECT(ROW()&":"&MIN(IF(P14:P102="",ROW(P14:P102)))))*(INDIRECT("P14:P"&MIN(IF(P14:P102="",ROW(P14:P102))))
View 9 Replies
View Related