Data Validation From Another Worksheet

Sep 27, 2012

How I can put a List on a separate workSHEET and refer to this worksheet when inserting a data validation list on my main worksheet?

View 4 Replies


ADVERTISEMENT

Data Validation From Another Worksheet

Oct 8, 2008

I would like to impose data validation on Sheet1 row A. I have a list of values in Sheet2 row A. Is their an easy way to do this?

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

Protect Worksheet And Data Validation

Oct 27, 2009

I have my spreadsheet protected and all the neccessary fields locked/unlocked, but one of the columns contains data validation. How can I prevent a user from copying and pasting a value into the data validation cell versus of course the intended purpose of picking from the list?

View 9 Replies View Related

Protect Worksheet And Data Validation

Oct 28, 2009

I have my spreadsheet protected and all the neccessary fields locked/unlocked, but one of the columns contains data validation. How can I prevent a user from copying and pasting a value into the data validation cell versus of course the intended purpose of picking from the list?

View 9 Replies View Related

Data Validation List From Another Worksheet

Jul 25, 2006

I'm using data validation to create a drop down list ina spreadsheet so that users can select from a pre-defined list. I want to be able to maintain this list in a separate worksheet, but when I try to create the list it tells me that "You may not use references to other worksheets or workbooks for Data Validation Criteria". I don't want to maintain the list in the same worksheet as there's a risk of deletion. Is there a way to create a dropdown list in a second worksheet?

View 5 Replies View Related

Using A Cell On Another Worksheet As Input For Data Validation

Oct 13, 2008

Situation: I have an Excel file with multiple worksheets. Each of these worksheets contain the same rules for data validation in a certain column (G).

Problem: If I want to add an item to the data validation lists I have to edit every single worksheet. I've tried making a seperate worksheet containing data validation items and using that as the source for the data validation rule, but Excel doesn't allow me to switch worksheets while defining the source.

In other words, I want to manage my data validation rules of multiple columns in multiple worksheets in one central location.

View 4 Replies View Related

Toggling Data Validation Cells From Another Worksheet

Feb 3, 2014

Is there a way to have a user on sheet1 pick from a pull down of a data validation on sheet2? I have important information that is linked to only sheet2 within a data validation. I want to be able to reference it in sheet1 to have the user pick, then it toggles them on sheet2. Is this possible?

View 2 Replies View Related

Reference Data Validation List On Another Worksheet

Apr 22, 2008

I have a spreadsheet in Excel 2003 with many sheets all of which need to have for some columns the data entry restricted to a specific list.

To do this we have created lists defined and held in a separate sheet and then attempted to reference these from all the other sheets using Data -> Validation and then entering the relevant list in the Validation criteria.

The 1st sheet we set the Data Validation up in all is fine. The 2nd sheet however I get an error pop-up stating 'You may not use references to other worksheets or workbooks for Data Validation criteria'.

View 4 Replies View Related

Data Validation Using List In Another Worksheet - Works For All But One Sheet!

Dec 30, 2008

I am running into an odd issue and hope someone might be able to shed some light.
I have created several lists on one sheet in a workbook and have assigned named ranges to each individual list. So far, so good.

On various other worksheets, I have created Data Validation rules to allow users to select the relevant data from drop-down lists. (using formulas such as =List1, =List2, etc. in the Source box to capture the named ranges I had created).

Everything works perfectly...EXCEPT for one worksheet, which continues to give me the error message, "You may not use references to other worksheets or workbooks for Data Validation criteria." Can anyone explain why this error would only occur on one worksheet and work fine for all the other sheets? I'm perplexed!

View 3 Replies View Related

Data Validation List To Select Worksheet In Macro

Apr 8, 2007

I have a Data Validation list in cell A1 of a worksheet called LIST. I would like to modify the macro that I have recorded, the macro uses data from various worksheets. I would like to sort out some code so that at certain points in the macro it will select the worksheet that is shown in cell A1 of the LIST sheet and then carry out the code already recorded. THis will allow me to quickly change a sheet name that is refererred to in the macro numerous times without actually manually editing the sheet name in the macro code.

View 7 Replies View Related

Data Validation Doesn't Work When Worksheet Populated From Userform?

Feb 21, 2014

I have this formula =COUNTA($A:$A)<=4 that limits amount of cells that can be populated in column A, I use data validation with "Allow costume" option and using that formula. It works fine from worksheet it displays the message when the limit is reached but it doesn't work when data is inputed/populated from userfrom, it allows userform to put more entries than set limit 4 in this case.

View 3 Replies View Related

2003 Data Validation Doesn't Trigger Worksheet Change

Dec 31, 2008

I have a bit of code that calls a formatting sub depending on which cell is modified. It is triggered by the Worksheet_Change event, determines which cell is modified, and either calls the formatting sub or doesn't based on the location of the modified cell.

Some of the columns in the sheet have data validation with drop downs. If I select a value from the drop down, it doesn't trigger the Worksheet_Change. If I type a value into the same cell, it does.

This was apparently an issue in Excel '97, but supposedly fixed in '03?

View 9 Replies View Related

Nested Data Validation: Figure A To Enforce Dual Data Validation On A Single Cell?

Aug 19, 2009

I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.

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: Can I Use Data Validation In A Cell That Has Formulas In It?

Apr 3, 2009

I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.

View 2 Replies View Related

Data Validation :: Selected From 4 Validation Lists

Sep 26, 2007

I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?

for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...

(I do not want to use a combo box for this)

View 9 Replies View Related

Create Autofilter Using Validation Based On Table In Another Worksheet?

Feb 25, 2013

Can I validate data based on a list in another workbook? is what I mean to say.

View 2 Replies View Related

Data Validation - Add Some More If / Else But Validation Don't Allows It?

Jun 30, 2014

I had some of values in Column A, B & C for ex: column A has brand name, column B has model name and Column C has sub_model name here i have a limited values i need to make it as drop down list but i had a problem with the below formula.

=IF($F$2=Sheet2!$H$2,al_v,IF($F$2=Sheet2!$H$3,am_v,IF($F$2=Sheet2!$H$4,au_v,IF($F$2=Sheet2!$H$5,be_v,
IF($F$2=Sheet2!$H$6,bmw_v,IF($F$2=Sheet2!$H$7,bg_v,IF($F$2=Sheet2!$H$8,cv_v,I
F($F$2=Sheet2!$H$9,ch_v,IF($F$2=Sheet2!$H$9,ch_v,"")))))))))

i need to add some more ifelse but the validation don't allows it.

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

How To Use INDEX Formula In Conjunction With Data Validation To Output Data

Jan 9, 2014

Essentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.

Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).

Also, the Data Validation dropdown includes blanks which I would like to exclude.

I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.

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

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; Get All The Data Row Wise For The Name Selected In The Drop Down

Jul 27, 2009

I have a excel sheet with values correcponsing few names. I have a list for a Data Validation drop down. How to get all the data row wise for the name selected in the drop down.

View 3 Replies View Related

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 donft 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) **ITfS 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 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

Data Validation Rules Not Moving With Data

Feb 24, 2014

creating a spreadsheet for work which is almost working a treat Unfortunately, when a row of data moves from one sheet (Queries) to another (Archive), data validation is lost. Initially I thought I daidn't have it set up on the Archive sheet, but on moving it back to queries (by use of a macro) the validation is still not working. how to keep validation rules WITH data when it is moved please? I will upload my file when I get home from work....network restrictions prevent me doing it here!

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

Creating A Data Form With Data Validation

Sep 15, 2006

I have limited ability with Excel, and I'm trying to create a database using the Data/Form menu choices. My problem is I only want specific data in two of the 20 or so fields. When I use Data/ Validation and direct data entry, I get exactly what I want, EXCEPT that the choices don't come up when I use the Data/Form. Users can enter anything they want, which defeats the purpose. I tried to build a user form to use for data entry, but I don't know how to get it to add items to a database or lookup items like the data/form process does. I also want to add a few macro buttons to the form to do other things.

I am pretty sure of a few things: 1) This should be pretty simple. Anyone knowing VBA would probably know how to do this in minutes. I found some code that did a simliar function, but couldn't interpret it to fit my needs, which brings me to 2) I'll never use VBA or complicated macros again. I don't have the need for them. This is just an isolated incident. Therefore, signing up for a class would be useless to me. I tried to locate some advanced Excel/VBA classes, hoping to find an instructor or even a student who would welcome a simple challenge, but there are none available.

View 5 Replies View Related







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