Allow User To Automatically Add Data To Data Validation List

Oct 27, 2009

I am trying to learn the concepts, then apply them to a set of actual worksheets. I am trying to use named ranges from one worksheet in another worksheet. The named ranges need to expand. Although I can get the add data to a dummy worksheet to work within the same worksheet and I can even get a named range to work in the foreign sheet.

My problem is, when I can end up adding the additional data to the named range, it will only add one name. I have a number of named ranges that are referenced on various worksheets. The idea is, certain named ranges should be able to expand so I donft have to type them all in. (There could be hundreds of manufacturers or models.) I would also like them to be able to sort themselves after additional names are added to the named ranges. I would also like them to auto populate as well. I am using Excel 2003 SP3 (11.8307.8221). I have enclosed the file. Drop down lists (named ranges) are on the worksheet called DROP DOWN LISTS (LOCKED) **ITfS NOT CURRENTLY LOCKED**.

The data validation issues are on the worksheet called Rev7.0 Wood (Basic Sheet.) There are other instances to use these, but I hope to learn to do them myself on the other worksheets. Of particular interest is Column f.................

View 3 Replies


ADVERTISEMENT

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

User Form Data Validation From List

Feb 8, 2010

The text box where the end user types in the data - I want to make this have a drop down list like when using data validation but dont have a clue how to.

View 5 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

Automatically Add To A Data Validation List

Jul 6, 2006

I've been using Dave Hawley's "Excel Data Validation List - Automatically Add to a Data Validation List" in my wb with great results, but now I am going to use a secondary wb to keep the Data Validation List and herein lies my problem. I lack the VBA skills to be able to modify the code and references so that when I input into the primary wb the data is updated in the secondary wb. The Primary wb is an xlt that I'm developing, each time a new xls is opened from the Primary.xlt, vba in the Primary.xls opens Secondary.xls. Secondary.xls has a named range "Names" which I wish to be able to update when data validation is performed in Primary.xls per the sample on this web site.

View 3 Replies View Related

Automatically Add To A Data Validation List ..

Apr 16, 2007

I'm trying to implement the feature from this great Ozgrid 'how to', and it's working for the most part, but the VBA falls over here...


Private Sub Worksheet_Change(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$F$10" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction. CountIf(Range("Dept"), Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Range("Dept").Cells(Range("Dept").Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub

Basically, I have a tab which contains 2 lists. One in Column A and one in Columb B. I want the corresponding dropdowns on a different sheet to allow entry of anything that's not on either of the lists.

I can get it to work if I place the lists on the same sheet as the main 'form', but I need to keep them on a seperate sheet (and as the 'how to' suggests the lists can be on any sheet, I thought it'd work 'out of the box').

View 9 Replies View Related

Automatically Add New Data To Validation List

Mar 5, 2008

trying to incorporate this in to a workbook in which name range is on a separate sheet to where the validation cell is but with no success could any one tell me how to adjust it so that it works,

View 8 Replies View Related

Data Validation List - Automatically Update Cells Where Used

Apr 15, 2011

On Sheet2, data validation is applied to cells D21:D70 to limit data to values to a list, which is a named range on another worksheet. There are ranges on two other worksheets with the same data validation rule. The range for the list is ‘Sheet1’!D12:D26 (which is named “Processes”).

On Sheet1, the user enters a list of their processes. Then on Sheet2 (and the other sheets where the data validation is applied), they select one of the processes from the list. This all works fine until they go back to Sheet1 and modify one of the previously entered values.

For example, "Brush Teeth" is entered as one of the processes on Sheet1. It is then selected on Sheet2. Then, the user goes back to Sheet1 and changes the value to "Brush Hair." When you return to Sheet2, the value is still "Brush Teeth." It can be manually be changed by selecting “Brush Hair” from the drop-down list (“Brush Teeth” is no longer available), but it doesn’t automatically update.

Data validation was applied to a single cell (A3) and the list was on the same worksheet (B1:B3).

Option Explicit
Private oldValue As String
Private Const DVCell As String = "A1"
Private Const dataList As String = "B1:B10"

[Code]...

'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in.

On Sheet2 (I assume I need to add this code to every applicable sheet), I tried editing the first part of the code as follows (changes in bold), but when I click on any cell on Sheet2 that has the data validation rule applied, VB returns the error “Run-time error ‘1004’: Method ‘Range” of object ‘_Worksheet’ failed.” If I go into the debugger, the second instance of “If Not Intersect(Target, Range(dataList)) Is Nothing Then” is highlighted.

[Code] .....

View 9 Replies View Related

Data Validation - When New Row Created / Dropdown List Automatically Creates

Apr 7, 2014

I'm trying to create a spreadsheet that has a data validation in it. I have a drop down list that has to options to select; Yes and no. Every time I create a new row to add more information I have to go through the process of creating a new data validation for for each cell in this coloumn.

Is there anyway of creating a way that every time I insert a new row, a data validation list box will appear in the coloumn that I want it in?

Sample Workbook: datavalhelp.xls

View 3 Replies View Related

Code That Automatically Run After Selecting Item In Data Validation Dropdown List In Column C

Oct 12, 2011

I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.

Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.

View 2 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

Data Validation - How To Create Dropdown List That Excludes Duplicate Data

Sep 26, 2013

I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.

For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?

View 5 Replies View Related

Data Validation: How Can I Stop The User From Moving On?

Apr 3, 2009

I have attached a sheet that I'm having an issue with. Columns D and E on the Dim Calculator sheet have a data validation formula in the cell. It works in the sense that it pops up if they are outside the parameters that I want them to hit. However, if the user clicks the Cancel button they can just keep going on even though the entry in the cell is aganist the validation.

Example: Columns D and E cannot be larger than Column C. Currently now if Columns D or E are larger than Column C the validation error pops up. But I can just click on cancel and keep going. That is what I want to prevent.

Is there a way to grey out the cancel button?

Should I do something else within the validation itself?

View 3 Replies View Related

Data Validation With User Formula Ability?

Feb 20, 2012

if it is possible to have data validations on a cell within Excel but allow a user to input a formula? The data validation is a decimal between 0 and 1 and the formula would be a vlookup.

View 4 Replies View Related

Load Rows Data On Selection Of Data Validation List

May 28, 2014

I want to load the actual data exist in the rows by selection value from the data validation list.

E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.

On the second sheet, i made a data validation list of all the person names

Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.

View 13 Replies View Related

How To Insist User To Fill In Cell With A Value For Data Validation

Oct 4, 2012

I have a file where I need the user to fill in a value in a single cell (A1) in order for the data validation I have set up in column B to work. Can I set up a second data validation in the cells in column B? Or is there a way to enter a data validation in cell B11 that would error the user if A1 is not filled in, and circle the cell (A1) that they need to fill in? B11 currently returns the sum of B3:B10.

A
B

1
Straight

2
Time

3
Sun

4
Mon

5
Tues

6
Wed

7
Thurs

8
Fri

9
Sat

10
Sun

11
Total Overtime

View 8 Replies View Related

Prevent User To Enter Duplicate Using Data Validation?

Dec 27, 2013

I want to prevent user from entering duplicate text or numbers in a cell using the Data Validation.

View 9 Replies View Related

Use Data Validation That Will Pull Data From A Source List

Jan 12, 2010

Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?

View 3 Replies View Related

Data Validation List: Ignore Blanks Between Data

Nov 1, 2006

In column A I have a list of text. There are blank lines in between the cell that actually contain text. What I am trying to accomplish is create a validation list that will give me only the cells with text in them and ignore the blanks. For example in column A1:A7 I have the following text:

John
Mike

Tony

Jake

My validation list will return those names but will also give me the blanks in between the names.

Is there any way to ignore the blanks?

View 4 Replies View Related

Excel 2007 :: Data Validation Based On User Input?

May 24, 2012

In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.

Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6

View 9 Replies View Related

Delete Column - Macro Using Data Validation User Input Value?

Nov 7, 2012

I would like to delete a column using data validation list. for example :

i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.

And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no.,
the selected part no. row should be deleted.

View 1 Replies View Related

Data Validation List With Changing Data

Jun 3, 2009

I would like to utilize data validation to create a drop down list from a list of 100 rows that changes monthly. Currently the list is set to draw data from A1:A100, however depending on the month, different cells will have data in them. What I don't like is currently the blank cells create gaps in the data validation drop down list and make it longer than it needs to be. Is there any way for excel to automatically just display cells with data in them in the drop down?

View 9 Replies View Related

Data Validation :: Limit The Data Available On The List

Jun 28, 2009

have created a list obx using data validation. My question is, is there a way to limit the data available on the list.

Example: sheet 1
A1 A2 A3
Address A
address p
address s Date

Sheet 2
has data validation list that consists of all of A1 a named range called "address". Everything works great.
I now want to be able to limit the available choices in the list depending on what cell the user is in

example: if user is in sheet2 a1, i only want them to be able to pick address that have an "A" or "p" in cell A2 on sheet 1

I tried to acomplish this using conditional formatting, I format my named range with different colors, but the colors do not carry over to the data validation list on sheet2.

View 9 Replies View Related

Data Validation: Automatically Add An Entry Into Its Database

Dec 5, 2007

How do I get data validation to automatically add an entry into its database.

Ex.
If a cell if formated for data validation and I make an entry thats not currently in its database, it enters it into its database.

View 9 Replies View Related

Update Data Validation Entry Automatically

Nov 19, 2009

I already have various dependent validation lists established using the INDIRECT function.

Now I want to figure out how I can automatically change the selected values given the selected list. For example:

Cell D11 is a validation list with options: Boat/Car

Cell D15 is a validation list where if D11 = Boat the options are Catamaran/Sailboat/Tugboat. If D11=Car the options are Convertible/Sedan/Midsize.

The problem is that if D15 is preselected as Sailboat, for example, and you go back and change D11 to equal Car, cell D15 remains an option from the Boat validation list. I would like it to automatically update as the 1st item on the car validation list.

View 9 Replies View Related

Data Validation - Control What User Enters Into Single Cell Based On Others

Nov 29, 2013

I want to control what a user enters into a single cell based on others.E.g.

A B
1 20 500
2 50 500
3 75 500
4 100 300

Column A is a thickness and column b is a width what I want to happen if the user enters a thickness in cell D1 and a width in cell D2 I want an error to pop up if for instance the user enters 100 as a thickness to only allow a maximum of 300 in the width. likewise if the user enters a thickness less than 100 in cell D1 to allow him to enter up to 500.

View 4 Replies View Related

Excel 2010 :: Data Validation - Restrict Value User Can Enter Into A Cell

Oct 2, 2012

I'm using Excel 2010, and I need to restrict the value the user can enter into a cell (E9).

In cell E3 is the screen width (pixels). eg 6024
In cell E5 is the preferred width of a window. eg 450

The user, in cell E9, enters an x coordinate for which they prefer the top left corner of the window whose width is specified in E5.

If the value that the user enters in E9, added to the width entered in E5, exceeds the value of E3, (if E9+E5 > E3) then the value should be disregarded (window will be off right of screen) and the user re-enter.

I'm not familiar with the use of data validation, so I'm uncertain as to how to use it in this circumstance.

View 3 Replies View Related

User To Select Top Left Of Data And Then Automatically Do Text To Columns

Feb 15, 2012

Data is input into my spreadsheet. It is not always in the same cell, and is not always the same length vertically. I want the user to select the top left of the data and then automatically do text to columns. Here is my code that is not working! :

Sub TextToCol2()
Dim rng As Range

Set rng = Application.InputBox(prompt:="Select a cell", Type:=8)
ActiveSheet.Range("rng:A" & Range("A65536").End(xlUp).Row).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True
End Sub

View 3 Replies View Related

Programme To Automatically Select A Value From A List Or Range Once A User Has Selected Corresponding Value From A List Within A Combobox

Jan 28, 2007

i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.

View 5 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







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