Data Validation Using Reference List From Another Sheet
Jun 2, 2009
I m using Data/Validation function in Excel2003? Is it possible to have reference list of drop down input from another sheet? I recorded the macro for this function and had the following result
Sub Macro1()
Range("A1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$C$2:$C$6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
How can I change reference List from the same sheet $C$2:$C$6 to "sheet2!$C$2:$C$6" or it is a limitation for this function?
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'.
Attached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.
Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.
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!
Originally I had a file with multiple tabs to sort data, this was becoming very ineffective so what I have done is modified the original WB posted into the desired new format. The WS PSM-04-03A is a vlookup based report that used to pull data from each tab based upon the WS Name (the code is still their) what I have done is combined the WS and made a column(ColumnD) for what used to be the WS Name. All WS have besides "Home" and "PSM-04-03A" have been combined into 1 WS and the title for the data from the original sheet names was added to columnD of "Recommendations" in the new file. (i.e. I did not add all files just some so you could see what I was getting at).
So using the two WBs as an example the Isomeration Unit/MEK Dewaxing Unit/ROSE Unit tabs have been combined into one tab and a new columnD added to signify what used to be done with the "tab". Now if you look at the validation on WS "PSM-04-03A" on the first WB it utilizes the WS Names to populate a list in cell A4 and cell C4 then populates with all columnA items from that WS. Now that I have combined the data I still need the validations to populate the same. So A4 in the new WB would populate with all non repeats of the unit in ColumnD of the WS "Recommendations" and then cell C4 would populate with the filtered columnA results based upon the columnD choice.
OLD FILE : (PSM-04-03) PHAR Tracking v2.xlsm
NEW FILE : (PSM-04-03) PHAR Tracking Modified.xlsm
I am attempting to put a drop down list using "Data Validation". I can get it working when my list is on the same sheet (sheet 1) and the column of cells I want the drop down list to show up in....(you know..when the drop down list shows up in each individual cell)......BUT...when I put the list on another sheet (sheet 2) and try to do the "Data Validation" back on sheet 1, excel won't let me go highlight the list on sheet 2.
I even tried writing sheet2 and the range and that still doesnt work.
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.
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
I'm trying to link a range of cells in one workbook to a range in another workbook, both in a network drive. Everything is working out well except for the cells that have a data validation list on them. the formula linking one cell is ='G:Destination To[Workbook2.xls]SheetX!B13 so I'm just trying to get the value in the cell. I don't need the validation list to transfer just the value of what was selected. If I go to that cell and type something in then it transfers over just fine. If the validation list was used in Workbook2 then I get a #N/A in Workbook1 that is linking to the Workbook2 cell.
How can I link the cell with a data validation list in it to a cell in another workbook so that I can get the data in the cell (whatever was selected from the validation list)?......
find attached the Report file. I prepared two sheet in this file and also add my question in the file. my question is about validation selection and get data from other sheet.
I would like to create a drop down list. So far I have done this using Data/Valadation. I like this method alot, but would somehow like to put the data on a seperate sheet in the workbook. Data/Valadation doesn't enable this. Anyway around this?
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.
I have a cell that I want to use data validation on so I have a drop down list. Problem is the location of this list will be in another workbook.. Is this possible to go from one workbook to another using data validation?
Also, depending on the information that is selected from the drop down list I want a cell to the left to pick the corresponding data from the list in the other workbook. These forms will always be in the same folder.. Not sure if that information is necessary but just in case you need to know.
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?
I 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)
In the following example the costs for each FGI SKU are on detail cost build sheets each named simply 600001, 600002, etc.
To add a summary I simply list all the SKU's on a summary worksheet and reference each detail sheet. The problem is with large numbers of SKU's it gets clumsy having to create a reference for each sheet.
Is there a way to have the formula in the cost column reference the detail sheet based on the value in the FGI SKU column?
is 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.
I need to get data copied from sheet1 to sheet2 depending on selection in a validation list.
The data is on the same row and all the same range. So it would look something like this. A B C Fruit Orange Apple Pear Veg Potato Carrot Onion Animal Bear Cow Dog
I have the data in A in a dynamic range and validation list. I need all the data copied from the specific rows ie choose Animal from the validation list in Sheet2!A1 and the entire row Bear Cow Dog get copied to Sheet2!B1, Sheet2!C1, Sheet2!D1.
The second problem I think is a lot more complex. So the same scenario above but this time its a multi select on Sheet3. I need to be able to select two (or more depending on Fruit and Animals to display in A1, A2 and then their valid options to appear in B, C and D
I am sure it is easy as I have done this before, but can't remember how or where I have put the code.I have a spreadsheet with multiple worksheets (The amount and names of sheets will keep changing). On a front sheet I have a dynamic validation list in cell "L9" that will always have the full list of spreadsheet names. The idea is that I will choose which spreadsheet to print from this list. Press a button and only that sheet will print.
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?
How do I write a reference from a drop down list to another sheet and have the formula do the if function so that it can do several arguments and return "direct" if true and "indirect" if false.
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?
