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
MsgBox "You must enter one of the following in this cell:"
.EnableEvents = False
.EnableEvents = True
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.
In my spreadsheet, on ( sheet A) I pick up a value to a range (O2:O22) from other worksheet (sheet B), the value is validated when a cell in the same range but other column (A2:A10) reach a specific criteria.
But in the same column if the criteria is typed again I gone a have the same value on range( O ), and I dont want that, because this value represents a total for a day, and is to be added to other cell.
I'm using this, to pick up the data Ex: (A2:A10) Criteria (O2:O10) value picked up
on SheetA, Sumif(sheetBA10:A40,A2,SheetBK10:K40)
How can I count only one of the values picked up from sheet B?
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.
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData() Dim counter As Integer counter = Sheets("Imported Data").Range("Counter")
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
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.
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.
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.
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.
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.
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?
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.
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.
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.
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.
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.
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..............
I have a sheet that contains two values in seperate columns that I need to concatinate into one value for the drop down. If I use one value my code works but doesn't show all the values. If I use both values I get a 1004 - Application Defined or object defined error. I bolded the section that if I add I get the 1004 error. Is there some sort of restriction when creating the validate list for a drop down? My list is not reaching 65000 characters which is the string cut off.
Dim intAdEventCol As Integer, intAdEventNmCol As Integer, intRow As Integer Dim intLastRow As Integer, intLastCol As Integer Dim strAdEvent As String, strAdEventNm As String Dim strAllAdEvent As String
Do Until intRow > intLastRow strAdEvent = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventCol).Value strAdEventNm = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventNmCol).Value If intRow = 2 Then..........................
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
Hoping someone would be able to help me with this. I have a sheet (example attached) and this sheet has a number of varying description types in the W coloumn (usually approx 10,000 rows). This field is manually input so there could be spelling mistakes and/or non standard descriptions.
What I would like, if possible, is a macro that would look at the D column and if this is 'GENERAL LEDGER', it would then look at the W column.
An input box would come up, and would list the different descriptions it found in column W, and number them. It would only list each different description once.
1. Bank charges 2. Bank charge 3. Cash 4. Fund Custodian Fees 5. Fund Manager fee 6. Interest income cash account 7. Interest income cash acc 8. Miscellaneous expenses 9. Miscellaneous income 10. Other income 11. Sec lending comm 12. Sec lending commission income 13. Tax Reclaimable - Dividends 14. Withholding tax dividend
The user would then be able to type in the corresponding numbers, if possible seperated by a space, comma or semicolon and the macro would then run through the sheet and delete the entire row if D was GENERAL LEDGER and W was the selected description.
I know that I can return the value of a defined name range, the address, and even the value of the define name, but if you are given a range address, how do you find its corresponding defined name in code?
I am creating a spreadsheet where the user enters food they have eaten that day. The way this is done at the moment is with validated list boxes, one depending on the other using the indirect function. I want to be able to make this into a user input form. Is there a way of adding these validations from the spreadsheet, to an input form. I want the input form to have an input for the day, food group, food item, and quantity of the food. The food item needs to depend on the food group.
Can anyone help me to get a validation to check that what is entered in a cell, lets say A1, has to be number between 100000 and 899999 (this I already have) OR a value/text from I100000 to I899999. So it only acceots a vlaue betwwen 100000 and 899999 and it is also ok to have the letter I at the begining.
I had an idea that I could use the level of indent in the first column to provide the name for the range. The easiest way to explain is with the example spreadsheet I have attached
Cells C5:C10 show how the naming convention should look like, basically Indent Level 0 returns a prefix (Sheet_Name_Prefix) and its corresponding row entry in Column A. Level 1 should return the last Level 0 name and its corresponding row entry in Column A.
I had a go at the code and it works for Level 0 but I can't get it to put to Level 1.
'Dimension Variables Dim RowTitleEntries As Integer Dim NameLoop As Integer Dim IndentLevel As Integer
'Set the value of RowTitleEntries RowTitleEntries = 6
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...