Validate Cell Entries To Named Range List

Apr 28, 2009

I have been trying to figure out how to use a named range (on another sheet) to validate the user entered data in a specific column. All my attempts at utilizing worksheet_change event have ended in errors. I've created a simple workbook that shows what I'm trying to do and have attached it to this message. The 'Sample (Data)' worksheet has the data table that would be completed by the user. On the 'Validation' worksheet I have named the range to be used to validate the data as 'rngVal'. So when something is entered under the 'Expense Type' heading, the code would verify that the value entered was contained in the 'myVal' validation list. If it was not present in the list, a msgbox would instruct you to try again. After stealing bits of knowledge and code from many of the posts on this site I cobbled together the following (which results in an error at the 'set rngFind' line):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFind As Range
'Set Target = Cells(ActiveCell.Row, ActiveCell.Column)
Set rngFind = Range("rngVal"). Find(Target.Value)
'If data in column C changes, do the following
If Target.Column = 3 Then
'If the value is found on the validation list, do nothing.
If rngFind Is Nothing Then
MsgBox "You must enter one of the following in this cell:"
With Application
.EnableEvents = False
.EnableEvents = True
End With
End If
End If
End Sub

View 6 Replies


Using Cell Reference As List Title When Transposing Named Range

Jun 21, 2014

I am building an interactive sheet to profile a number of divisions based on a summary sheet of about 1200 rows and 90 columns of profiling criteria

I have attached an example but essentially:

1. I would like to be able to select from a list a value which would also be a named range title
2. Have the range information transposed into a row

What works:
1. I can transpose the range information when I use the following formula
I used OFFSET(Governance,COLUMN()-MIN(COLUMN(HGovernance)),0)

The problem:
I cant get it to use a cell reference (i.e. When I select Governance out of a Drop down list of Finance, HR, Governance etc). Having to type in "Governance" to bring back governance range information is fine, but not great when I am trying to profile the Finance or HR division as it then becomes too manual.

View 11 Replies View Related

Validate Numeric Entries

May 13, 2014

I need to determine if excel cell contains any character except number If it contains any character then place 1 in adjacent cell for example, otherwise 0

Column                 A                     B                           
150 000           1                           
150000            0                           
150,000           1                           
150.000            1                           
150000 kzt       1                           
150000kzt        1

View 5 Replies View Related

Duplicate Entries, Validate Only One

Jan 22, 2007

In my spreadsheet, on ( sheet A) I pick up a value to a range (O2:O22) from other worksheet (sheet B), the value is validated when a cell in the same range but other column (A2:A10) reach a specific criteria.

But in the same column if the criteria is typed again I gone a have the same value on range( O ), and I dont want that, because this value represents a total for a day, and is to be added to other cell.

I'm using this, to pick up the data
(A2:A10) Criteria
(O2:O10) value picked up

on SheetA, Sumif(sheetBA10:A40,A2,SheetBK10:K40)

How can I count only one of the values picked up from sheet B?

View 5 Replies View Related

Validation List Based On Named Range Not Restricted To List

Dec 15, 2007

I am attempting to use a named range as my Validation data source but have two questions:

1) It seems when you use a named range as a source the user can enter in any data they want in that cell. I really only want the user restricted to the list I give them. If I enter the list manually in the source box then it works but I really want to use a named range

2) My range is fixed to 10 cells and the user can enter in as many variables as they want (up to ten). When a user clicks on one of the cells that the validation is set to read the range the user has to always scroll to get to the top. When selecting the cell Ecel always defaults to the bottom choice in the list. I want to minimize the VBA because Mac users will be using this and VBA seems to be touchy with macs.

View 9 Replies View Related

Validate Cell Value Against Range Or Array

Apr 18, 2007

I would like to use VBA to validate the data in an Excel worksheet against either a named range or an array of valid data.

The valid data comes from an accounting system. It is a list of customer numbers. I am using ODBC and SQL to get the data into an array and then into a named range. This is working fine.

I want to validate the customer number column in my source Excel worksheet against either the array or the named range.

There are a variable number of lines in the source data.

I have a VBA procedure which starts at cell A2 and then works down column A until it reaches a blank cell.

I would like to use a VBA formulae which does something like:

IF active cell offset (0,3).Value in (named range or array)
Then set cell colour for active cell offset (0,3) = Green
Else set cell colour for active cell offset (0,3) = Red
End If

Or something like that.

Can this be done using VBA? Or do I need to create a new column in my source data and use VBA to place a VLOOKUP based on a named range into this new column?

View 9 Replies View Related

Determining If Cell Is Part Of Named Range And What That Named Range Is?

Aug 16, 2014

Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:

Code] .....

here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.

View 5 Replies View Related

Copy/Paste Cell To Named Range Named In Adjacent Cell

Sep 4, 2007

I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.

The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.

Sub PopulateWithImportData()
Dim counter As Integer
counter = Sheets("Imported Data").Range("Counter")

Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("imported data").Select

i = 1
Do Until i = counter
Range(Cells(i, 1).Value) = Cells(i, 2)
i = i + 1

View 8 Replies View Related

How To Assign List To Named Range

Oct 11, 2012

I have the following VBA code which created a unique list of values in a data column. After list is created, I want to assign it to a named range ReferTo parameter. Currently I am getting compiler error. How can I assign this unique list to a named range?

Dim UList As New Collection
Dim rCell As Range
If TypeName(Selection) = "Range" Then


View 4 Replies View Related

ComboBox Add Entry To Named Range If Not In List

Feb 4, 2014

I am trying to work up a referral tracker for my wife's employer. I have Named Ranges on sheet 2, a running log on sheet 1. I also have a userform that is being populated from the ranges on sheet 2. I have the project mostly functional, except for a feature I would like to add in. I am interested in adding to the named range that is being accessed if the entry is not in list, via the combobox1 on the userform. Also, if possible, I would like the list to re-sort behind the scenes so the added entry is properly located in A-Z format for next time. After searching the web a bit, I found some code that might work with some tinkering, but currently I am having issues with it. Here is the bit I am trying to use.

[Code] ....

I have been getting 424 Object Errors and a few others as I continue to mess with this. I am also attaching the project if someone may see a better way of getting the task accomplished. The overall scope of this is to log all referral sources so monthly and yearly reports could be made.

Attached File : Copy of Referal Tracker.xlsm‎

View 3 Replies View Related

List Unique Values From A Named Range

Dec 4, 2007

I am trying to find a way to create a list of unique items from a named range. I have found a few solutions using filters for ranges that are contained in the one column but not named ranges that contain multiple rows and multiple columns.

Is there any way to copy all of the unique items from the named range to another location? Lets call the named range Table1 and it spans from B2 to J100.

View 9 Replies View Related

Transpose Named Range For Combo Box List

May 29, 2006

I have been trying to make a combobox for which the list is a named range. However, this range needs to be transposed. ListFillRange doesn't seem to let me transpose the list first. I've tried transposing the list somewhere else first and then adding it, but it seems to want a range as opposed to a reference to a range. I'm so confused now. This is the basic code that I wish would work.

ActiveSheet. OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=253, Top:=472, Width:=117, Height:=20). _
Selection.ListFillRange = "=transpose(Stream_Data!StreamList)"

While I'm at it, could someone explain to be how to refer to a combobox. ie. when you create it, you don't name it so how can you refer to it. That's why I have used the selection tool above to add the list.

View 3 Replies View Related

Create List Of All Named Range Names

Oct 17, 2006

I have a worksheet where a number of the cells have been assigned names. I want to copy these cell names & create a list on another part of the worksheet. Does anyone have some code I could use to achieve this?

View 8 Replies View Related

Validation List To Combo Box Without A Named Range

Dec 5, 2007

I'm creating a sheet that takes three Validation Lists into account. The first two lists are based on named regions. The third list uses the first two lists to narrow a search down so that the user can pick what they want from a smaller selection. I'm using the code from to allow the user to double click the validation list to pop up a Combo Box. This allows me to show more than 8 selections at a time and also gives a little freedom on font and font size.

Since the third list is not based on a named region but rather code to compare the two previous lists, when I double click the third validation list a blank combo box pops up. it possible to pop up what ever lists are in the validation boxes.

This is the code that I have in the third validation box which States "The Source Currently evaluates to an error" but works fine for some reason. =OFFSET(CategoryStart, MATCH(B4, CategoryColumn, 0) - 1, IF(A4 = "Description", 2, IF(A4 = "ExtDescription", 3, IF(A4 = "PartNumber",4))), COUNTIF(CategoryColumn,B4), 1)

I tried making a named region that was based on the code, so that the list could change as the user selected different options from the first two lists. This gave me the same problems as I had before.

View 2 Replies View Related

Produce A List Of Text, After Selecting From Validate List

Jun 5, 2009

i want to do is, have a list that picks from a color and then beside it, it will show the possible fruit that is that color for example




The number of fruit will differ by the color so i know i need a counter there, but i am not sure how to list the fruit after i've picked the color

View 6 Replies View Related

Validation List Only Partially Sees Named Range

Feb 25, 2009

I've got a named range which defined using a custom function. Lets call this named range [range1] which is defined by:

View 14 Replies View Related

Data Validation List With Criteria Using Named Range

Oct 30, 2012

I have been tasked with creating a data validation list. My data is contained on a separate sheet from my data validation list. I am using a named range to use the data for my data validation list. However, I am looking to add a criteria to my data validation list so that it only is populated with values based on another column on my data sheet.

For example, if I have a data sheet called "Employee" and I have two columns. Column A is a list of employee names and I have named a range in column A as "EmployeeName". Column B is used to define the category of employee. This value is a letter, for example, "A", "B", "C", or "D". This column does not have a named range as I don't know if I need one to get this to work.

I would like the data validation on one sheet to use a list of employees of a particular class from another sheet. Straight forward validation is easy using a validation list with a formula =EmployeeName. But how do I only list employees that have a category "A"? For some reason, I cannot get some of the formulas I have found while searching this message board to work.

View 7 Replies View Related

Dynamic Named Range Linked To A Drop Down List

Feb 27, 2007

I purchased the Excel Hacks book, and when attempting to do hack #53 with a drop down list, I can not get it to work. I do exactly what the book says to do, however, my chart and my drop down list are not linked together, and when I change a name in the drop down list, the information on the chart does not change.

View 2 Replies View Related

Dynamic Named Range Drop-down List To Exclude N/A Errors?

May 7, 2014

I have a dynamic named range which allows me to have an automatically growing drop down list in B1 as more cities are added in column A. however, my workbook will have N/A errors to begin with before cities are added.

is there a way for my drop down list in B1 not to include the N/A errors and only the cities? of course when the next N/A error turns into a city, i would like the B1 drop down list to recognise this and include it.

View 4 Replies View Related

Selecting Named Range From Dropdown Validation List To Use In Formula?

Jan 18, 2013

Basically on a summary page, on sheet 1 say I have a formula which picks up the unit cost (I have this as an array across 5 years)

What I then want to do is multiply this by a volume driver. I have a list of these drivers on a seperate page, so for convenience lets say DRIVER_01,DRIVER_02 etc. These are also 5 years arrays.

So what I want to do it be able to select which driver I want to use for volume on the summary, from a drop down box I have set up, and then the formula pick up that named range, and not just recognise it as a text cell.

I need the ability to change the driver in the future, hence the drop down box. I can also get the unit array part to work fine, I'm just struggling to get this driver array to work..............

View 2 Replies View Related

Renaming A Named List Or Adding To A Named List

Jan 16, 2009

So I created a list


and named it "Alphabet"
but now I have E and F and would like to make the list


and name it "Alphabet" again

however, when I select it all and make it Alphabet, it selects A to D automatically

is there any way I could make A - F named Alphabet?

View 7 Replies View Related

Validate List - Restriction

Jul 1, 2009

I have a sheet that contains two values in seperate columns that I need to concatinate into one value for the drop down. If I use one value my code works but doesn't show all the values. If I use both values I get a 1004 - Application Defined or object defined error. I bolded the section that if I add I get the 1004 error. Is there some sort of restriction when creating the validate list for a drop down? My list is not reaching 65000 characters which is the string cut off.

Dim intAdEventCol As Integer, intAdEventNmCol As Integer, intRow As Integer
Dim intLastRow As Integer, intLastCol As Integer
Dim strAdEvent As String, strAdEventNm As String
Dim strAllAdEvent As String

Do Until intRow > intLastRow
strAdEvent = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventCol).Value
strAdEventNm = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventNmCol).Value
If intRow = 2 Then..........................

View 9 Replies View Related

Find Matched Value In A Range For Named Cell Then Copy Range Cells Below

Aug 6, 2013

I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.

The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.

View 9 Replies View Related

Display A List Of Cell Entries, Allow The User To Select Which To Delete

Sep 16, 2009

Hoping someone would be able to help me with this. I have a sheet (example attached) and this sheet has a number of varying description types in the W coloumn (usually approx 10,000 rows). This field is manually input so there could be spelling mistakes and/or non standard descriptions.

What I would like, if possible, is a macro that would look at the D column and if this is 'GENERAL LEDGER', it would then look at the W column.

An input box would come up, and would list the different descriptions it found in column W, and number them. It would only list each different description once.


1. Bank charges
2. Bank charge
3. Cash
4. Fund Custodian Fees
5. Fund Manager fee
6. Interest income cash account
7. Interest income cash acc
8. Miscellaneous expenses
9. Miscellaneous income
10. Other income
11. Sec lending comm
12. Sec lending commission income
13. Tax Reclaimable - Dividends
14. Withholding tax dividend

The user would then be able to type in the corresponding numbers, if possible seperated by a space, comma or semicolon and the macro would then run through the sheet and delete the entire row if D was GENERAL LEDGER and W was the selected description.

View 9 Replies View Related

Match Named Range To Range/Cell Address

Aug 25, 2006

I know that I can return the value of a defined name range, the address, and even the value of the define name, but if you are given a range address, how do you find its corresponding defined name in code?

View 4 Replies View Related

Data Input Form Not Validate List Boxes

Dec 8, 2009

I am creating a spreadsheet where the user enters food they have eaten that day. The way this is done at the moment is with validated list boxes, one depending on the other using the indirect function. I want to be able to make this into a user input form. Is there a way of adding these validations from the spreadsheet, to an input form.
I want the input form to have an input for the day, food group, food item, and quantity of the food. The food item needs to depend on the food group.

View 9 Replies View Related

Validate For Numeric Range & Alphanumeric Range

Dec 20, 2007

Can anyone help me to get a validation to check that what is entered in a cell, lets say A1,
has to be number between 100000 and 899999 (this I already have) OR a value/text from I100000 to I899999. So it only acceots a vlaue betwwen 100000 and 899999 and it is also ok to have the letter I at the begining.

View 9 Replies View Related

Dynamic Range Used Named Cell Range

Jul 7, 2014

Line of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.

Range("DataSummary),(??,??) doesn't work.

View 1 Replies View Related

Named Ranges Based Upon Indent Levels & Entries In Column

Sep 19, 2008

I had an idea that I could use the level of indent in the first column to provide the name for the range. The easiest way to explain is with the example spreadsheet I have attached

Cells C5:C10 show how the naming convention should look like, basically Indent Level 0 returns a prefix (Sheet_Name_Prefix) and its corresponding row entry in Column A. Level 1 should return the last Level 0 name and its corresponding row entry in Column A.

I had a go at the code and it works for Level 0 but I can't get it to put to Level 1.

Sub Test()

'Dimension Variables
Dim RowTitleEntries As Integer
Dim NameLoop As Integer
Dim IndentLevel As Integer

'Set the value of RowTitleEntries
RowTitleEntries = 6

View 3 Replies View Related

Named Ranges On Multiple Sheets With The Same Named Range & I Cant Figure Out How To Do This

Jun 2, 2006

I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...

View 2 Replies View Related

Copyrights 2005-15, All rights reserved