Is it possible to have a workbook (1) with a list on it and another workbook (2) which has a dropdown list on it that shows the list on the 1st workbook. But when a new entry is made on workbook (2) it is added to the list on workbook(1)
View 8 RepliesI have two workbooks.
Workbook1 contains validated list which is defined by a Range name and also its data is pulled from workbook2. I am unable to change the value of the validated list from drop down when i close workbook2, infact the validation drop down list do not drop at all so that can select values from it. But if i open the workbook2 i am able to change the values of the validation list.
I want the dropdown list to be active when i close the second workbook.
I'd like to create a list via Data validation-list. Anyone knows how to reference a range on another sheet of the workbook?
The range is =$Q$5:$Q$13
Is it possible to reference another sheet? If yes, how is is done?
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
Is it possible to have a list from a different workbook as the source list for a validation dropdown on a cell? If so, what is the syntax.
View 2 Replies View RelatedIt can be done using named ranges. Name the range of the source list and set the validation to List =name. Sheet location does not matter.
I can get it to work using Indirect - but this puts a limitation on the process that the source must be open. Mike, perhaps you could explain more fully how you are achieving this?
I know it's possible to list option reference in another worksheet in relation to data validation, but is it also possible to reference to a complete different workbook. If so, what's the procedure? I have many workbooks that reference the same list options.
View 7 Replies View Relatedi just want to generate a list validation where source resides in another workbook?
i have used named range concept ?but not working can anyone help..
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 RelatedI'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.
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 RelatedI 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)
dear....can i set width in list box data validation (width in cell i set short).thanks
View 9 Replies View Relatedis 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 RelatedI built a template which pulls data from the source file using formulas. the problem is formulas doesn't give any error it updates the right value when the source workbook is open.
I Thought problem could be with the formula and tried just Source A1=destination A1. it also updates only if the source file is open. when the source file is closed it shows the last captured value.
The source file is on the (Work) Network Drive and is accessible to few people. my template is password protected (tabs) so formulas cannot be deleted.
Using Excel 2010.
I have a formula in a workbook that links to another workbook and returns a #VALUE! error. The formula is:
=OFFSET(INDEX('\srvrdatakenny cantrell$Kenny''s FolderPayroll[DI Staff List.xls]Sheet1'!$D$6:$D$60,MATCH(TRUE,'\srvrdatakenny cantrell$Kenny''s FolderPayroll[DI Staff List.xls]Sheet1'!$D$6:$D$60=B3,0)),0,2)
However, when I open the source workbook, the cell updates correctly. How can I get the cell's formula to update when the source data workbook is closed?
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?
I have a problem in creating data validation. I want to make 3 drop lists which are connected to each other (file1.xls for example). The sample is like the tutorial from Contextures: http://www.contextures.com/xlDataVal02.html
The source of lists are from another excel file (file2.xls). The method is also from Contextures: http://www.contextures.com/xlDataVal05.html And then, when I tried this, the second drop list didn't show up according to the first drop list. But, if I put the source in the same workbook (in file1.xls), It could show up. I think the problem is in the name box, but I don't know how to fix it.
I've got a list of text files I need to import into different worksheets in xl. The list is of the file name and the column start points for the fixed width file. This will be a regular job so if can automate it would save alot of time. I've record a macro that does text to column and adapted it to create an array listing the start points for the particular file, but when I try to use an array name with TextFileFixedColumnWidths, it doesn't seem to work.
View 2 Replies View RelatedI'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?
The idea is to create a liquor order sheet which takes items and prices from a liquor inventory workbook, which is part of the same file. i'm sure that you can do it all fancy with a macro, but unfortunately i'm not super experienced with excel. so i helped myself with what i know a bit -> drop down lists. I created an extra sheet which has 4 ranges on there... the liquor type (liquor, beer, wine), and the 3 sub classes for each type (red/white/blush/sparkling for wine, draft/import for beer and so on and so forth). On the inventory i named the range of all white wines "white", of all red wines "red" ..
On my order sheet i created a drop down list for type and created 2 more via "indirect" .. so when you select "wine" in the first one, the 2nd one will ask you for white/red/blush/sparkling and the last drop down then for the exact item according to the range on the inventory.
Hope what i did so far is understandable ^.^
Now my problem is that i want the unit cost to be copied when an item is selected. So you select "beer" in A1, "import" in A2, "corona" in A3... and the price for corona (which is written in a cell on the inventory sheet) should show up in A4.
I attached a screen of the inventory sheet so that you can see how it is structured.
Hello, could some one please help me with the following:
I have created a drop-down list for the range : C3:C65000 using Excel 2003 and Windows XP Pro. I did this using Excel's Data Validation Tool. The settings I used are as follows:
Allow: List
Source: =GROUPS
Check Box Ignore Blank is Ticked
Check Box In-cell dropdown is Ticked
GROUPS = GROUPS!$A$2:$A$29
Now all this works well, such that when I click in the cell range : C3:C65000, then a dropdown list appears - showing a list of all my groups.
However, when I click on any of the dropdown lists in this range, the width of the dropdown list is only as wide as the column. Column C has a width of 20.
What I need please is for the dropdown list to be as wide as the list of information showing in the dropdown list. If some one could please explain on how I can achieve this - that would be great.
I have a workbook that uses data validation from a list.
The same list is used in several of the individual sheets.
Can one use just one list for different sheets, as I'm having to create separate list for each sheet, which when an overall change needs to be made it is easy to miss one.
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.
I have data validation on all the pages in my workbook. How do I edit all the data validation at once.
View 4 Replies View RelatedI have a cell "project titles" entered in sheet1 that populates into sheet 2 - column "Project titles".
I need to keep the values in this column unique. So can I create a data validation such a way that I won't be able to enter values into this cell that are already on the list. This way I can avoid entering duplicates.
I try to find a solution (with or without macro) to assign different ranges named "Failure_C..." in a data validation source depending on the value of another data validation list (32 values called)
I started in the Data Validation List Source to fill in IF function but 32 inputs are not accepted and too much caracters in the source.
The formula is starting like that:
=IF(A57=$M$78,Failure_C01,IF(A57=$M$79,Failure_C02,IF(A57=$M$80,Failure_C03,
IF(A57=$M$81,Failure_C04,IF(A57=$M$82,Failure_C05,
IF(A57=$M$83,Failure_C06,IF(A57=$M$84,Failure_C07............
I am trying to make a simple planning sheet.
I have a workbook with 2 sheets.
1.sheet contains Columns : DayDateTimeClassTeacherCovered
2. sheet contains the availability of teachers. so all columns start with a date and the rows contain the name.
A row therefore looks like this.
7/1
Harley
Lidia
Livia
Thais
Salete
Carla
My issue: The 1.sheet has a column Teacher, now in this column I want to have a drop down list. (E2, E3, E4, E5 ... etc..) This list is depended on the date which is in column B.
Essentially I want the list to look up date in its row go to the second sheet and give me all the possible names in it. That is all.
I have a list on one worksheet and I use a dropdown list from Data Validation to select the name required.
My list is so long that I would like to be able to type a letter or two to access to the name quicker instead of scrolling down.
I copied a formula that worked for someone else that put this formula in the data validation
(IF(I36<>"",OFFSET(Player,MATCH(I36&"*",Player,0)-1,,SUMPRODUCT((MID(Player,1,LEN(I36))=TEXT(I36,"0"))*1)),Player))
I do not wish to use VBA is possible.
I am trying to create a data validation list with a bar graph. I have tried using the IF and Vlookup formula, but its not working for me. I would like to be able to select classroom # from the drop down menu and the bar graph update selected with student’s name and selected data information. I have attached the file.
View 5 Replies View Related