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 Contextures.com 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.
http://www.contextures.com/xlDataVal11.html
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
ADVERTISEMENT
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). _
Select
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
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
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
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
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
Sep 16, 2007
I've got a Combo Box that I want to be able to attribute Hyperlinks to. I would like to be able to select one of the options from the Combo Box and have the Hyperlink fire automatically. The conditions are:
Workbook Name: Floor Plan
Combo Box Worksheet Name: Switched 2
Input range: 'Combo Box'!$A$1:$A$4
Cell link: $CU$80
Drop down lines with corresponding Hyperlink Named ranges:
R FRONT R_FRONT_Hardwired
L REAR Audio_1_AZ80
120V-60HZ 4A ER_16_PC_100A_Outlet_6
R REAR Audio_1_AZ80
I've attached the Code as text.It is not working and I'm sure there are a number of reasons why.
View 2 Replies
View Related
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
Mar 25, 2014
Can you create a Pull-down List that contains data from Multiple Ranges.
If I have a List of Names Running down column A (A2:A10) another List of Names Running Down B (B2:B25) and a Third down C (C1:C15) each of them named Ranges ("List1", "List2" & "List3"), can I create a Pull-down list in Cell A1 that would include the names from all three ranges?
View 4 Replies
View Related
Feb 26, 2008
is it possible to refer to more then one named range in my validated list's source field? If not, how can i make more than 1 named range to be the source of my validated list.
View 9 Replies
View Related
Feb 11, 2010
Is there any way to use a Named Range in the "Source" field in a Data Validation? I have multiple sheets in a workbook that have the same layout, and I am using alot of different Data Validations. My problem is that whenever I need to change, or add a choice to one of these, I have to go to each sheet and change the Data Validations that are effected by that change. Is there a way to use a "Named Range" or something to put all of my acceptable choices, for that Data Validation, in one spot. And make any changes I had to that field.
View 2 Replies
View Related
Jan 28, 2013
I have a cell for which I need the only options for data to be entered from a list in two different tabs from which I understand cant be done but is it possible to have two drop down options for the same cell?
The attached spreadsheet should explain this slightly better...
View 9 Replies
View Related
Nov 13, 2006
In the attached example I have a column of data on worksheet 2. Extra rows will frequently be added and removed from the column. I want to use that column of data to populate a validation drop down box for a whole column of cells on sheet 1. (Target cells coloured yellow for demonstration purposes)
I don’t want the validation drop down to be full of blank rows so I set up a dynamic named range to refer to the column on Sheet2. It all works really well...but...only for cell D6 on sheet 2. It doesn’t work on any of the other target cells on sheet 2 or on sheet 1. I have been looking at this for far too long and it has begun hurting my brain.
View 2 Replies
View Related
Nov 7, 2012
If I have two cells and a named range mexico
A1, A2.
A1 contains the string: "mexico"
A2 is the cells that has required data validation (drop down list).
I want A2's validation reference to be dynamic, in the sense that I can it reference it to A1; converting the string "mexico" to a named range mexico
I tried inputting = INDIRECT(A1) into A2
But I lack clarity in my understanding of referencing.
What is the correct procedure here.
View 2 Replies
View Related
Oct 27, 2013
I've set up a Named Range that refers to an array of constants, e.g. MyRange ={1,2,3,4,5}. This appears to be fine since I can output the named range to a range of cells in a row (e.g. A1:E1) and see 1,2,3,4,5 (one value in each column).
I'd like to use MyRange as the source for a Data Validation list in a cell.
Normally data validation list sources must be vertical, so I've set MyRange =TRANSPOSE({1,2,3,4,5}). This appears to be fine since I can output the named range to a range of cells in a column (e.g. A1:A5) and see 1,2,3,4,5 (one value in each row).
When I set MyRange as the source for data validation however, it resolves to an error.
why this setup doesn't work?
Workbook detailing the problem attached. Attachment 273788 NamedRangeDataValidationArrayConstansts.xlsx
View 3 Replies
View Related
Sep 30, 2012
I am preparing a template for which I need a combo box. Becaue it is a template I can't have this combo box any specific input range. Because I won't know how long the data column (that wiill be used for input range) will be.
Also the data column will have many duplicates but the combo box needs to show uniques only.
View 1 Replies
View Related
Apr 30, 2009
I know how to turn the autocomplete on and off but I don´t have a clue where I configure the data range or the cells to display the list when the combo box is clicked.
I am assuming that this is done in the Edit mode at "linkedCell" and LisfFillRange ... yet I can´t get this to work.
View 2 Replies
View Related
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?
VB:
Dim UList As New Collection
Dim rCell As Range
....
If TypeName(Selection) = "Range" Then
[Code].....
View 4 Replies
View Related
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
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
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
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
Else
MsgBox "You must enter one of the following in this cell:"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
Else
End If
End Sub
View 6 Replies
View Related
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
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
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
Jun 22, 2009
I'm trying to fill a list with some hard values. When I use the macro recorder it adds formula="choice 1;choice 2; etc", these are all separate values in the list. However if I use this within a macro I only have one option with all three values combined.
View 4 Replies
View Related
Mar 12, 2009
I have a Range on one sheet formatted like this and sorted by Sup then Rep:
Rep Sup
John Sally
Mark Sally
Joseph Sally
George Betty
James Betty
etc..
I have a Validation Drop Down list where a Supervisor is chosen. There's a specific range that I would like to automatically fill in with the reps assigned to the Sup. This is driving me crazy! I already know how to obtain that list as another Validation Drop Down, but not quite sure how to make those values appear in a range instead of a Validation List.
View 2 Replies
View Related
Feb 5, 2009
I have a monthly report showing actuals and budget for current month (or quarter) and year to date. Range names have been created for (1) each month, (2) each quarter and (3) year to date). I'm using the SUMPRODUCT function to extract information by account. For example:
=SUMPRODUCT(($A9=ACT_Account)*(ACT_Jan))
This formula works except I have to replace the "ACT_Jan" each month accordingly. Can a data validation list be created with the range names so that updating this formula monthly can be simplified? For example, changing "ACT_Jan" to "ACT_Feb" with a drop down list.
View 9 Replies
View Related
Jul 16, 2006
I have a list box where I select "Income" or "Expense". In relation to that, In the next column called "Category", I use the offset formula in " Validation". Depending on whether I choose Income or Expense, I will get different dropdown options for "Category". What I want to do is if I choose the first column combo box, and leave the second column combo box empty, or Vice Versa, I want a messagebox popup saying that I have to choose an option from the dropdown. But if both are empty fields, then do nothing. I have attached the file called "Data Validation Test"
View 7 Replies
View Related
Nov 23, 2007
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?
View 9 Replies
View Related
