I've found that sorting a column next to one which has drop down boxes set up in it, does not take the options for the drop down boxes (data validation ) with them when being re-arranged. Is there a way to make this possible, or an alternative format I should be using? see example:
If item 4 is the only one available from supplier 3, (As shown by the drop down box options) how do I sort by say, department, and still have the correct options on hand? Sorting by department, the items move, but the data validation info stays where it is, leaving the supplier 3 option for item 1, which is not correct.
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.
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings() Dim iCTR As Integer Dim yCTR As Integer Dim zCTR As Integer
zCTR = 11 For iCTR = 12 To 23 For yCTR = 1 To 10 If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value zCTR = zCTR + 1 End If Next yCTR Next iCTR Range("AA11:AA" & zCTR).Select Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
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 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.
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 scoured the site looking to see if anyone had a similar problem of a data validation list suddenly not working. By not working I mean that it was impossible to get ANY validation list working on a given sheet at all once the problem manifested itself. Luckily I anally backed up my sheets as I went and was able to get back to a version that worked. I was able to trace the problem back to a minor logic error in a vb script that accidentally deleted an object me.shapes(4) that turned out to be the data validation object (ie "Drop Down 223").
With that said, I purposefully want to delete me.shapes(4) becuase it supposed to be a picture that I replace based upon a choice from the drop list. What I've discovered is that sometimes the picture is me.shape(4), and sometimes it's shape(3) and the Drop down is Shape(4) (strange eh ?). I guess I can live with it, but I can't seem to figure out the following line as to why it won't work.
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.
I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String With CreateObject("VBScript.RegExp") .Pattern = "reg_d+(_)+d+//d" GetString = .execute(txt)(0) End With End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.
I have Sheet1 "MASTER" and Sheet2 "Area1" and Sheet3 "Area2" etc...
My MASTER sheet has a list of employee names and the areas they work in. I have employees working in different areas, and I want to pull a list of employee names from the MASTER working in Area1 (sorted on the MASTER sheet) to column A on Sheet2, then pull a list of employee names working in Area2 to column A on Sheet3, etc...
I want it to do this in such a way that if I add an employee to Area1 on the MASTER data, it will populate that employee in the Area1 Sheet.
So basically, I'm looking at one column on the MASTER sheet to see if the area matches. Then looking at another column on the MASTER sheet to get the name. Then taking that name and transposing it to a new sheet corresponding to the area they work in.
I've attached a sample sheet. I want Column A in the Area1 sheet to reflect all names that show up on the Area1 LIST on the MASTER sheet, and nothing else. I'm using VLOOKUP to pull the rest of the data from the MASTER table.
There is a new sample workbook up now. Couldn't update it sooner due to site outage. I've removed irrelevant data to improve readability and focus on what I'm trying to achieve. Again, the main issue is scraping column E from the Master, and populating a list of all employees who match certain values in Column E on the Master in Column A of the other tabs.
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?
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.
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.
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?
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.
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.................
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.
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!
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?
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.
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.
I have 2 columns that need to be filled in once a name is selected from the data validation drop down. The data is to the right of the name in the source area.
Column A Column B Column C 1 Name 1 Skill 1 Phone # 2 3 4
This is the format, Every row in Column A has a Data Validation Drop Down, I need Column B and C filled with Data from the selected name from Column D (the data columns D-F)
Column D Column E Column F 1 Name 1 7 555-1111 2 Name 2 5 555-2222 3 Name 3 3 555-3333 4 Name 4 4 555-4444
So when I select say name 3...
Column A Column B Column C 1 Name 3 3 555-3333 2 3 4
In the attached version the "Print Version" tab sorts the values entered into the "Log Entries" tab. When entries are deleted from the "Log Entries" tab they are converted into 'blank' entries and moved to the end in the "Print Version" tab.
However, in earlier versions the "IFERROR" does not appear to work, therefore any blank entries are shown as "#NUM!", which is kind of annoying. If there any way around this? The forumla is shown below. =IFERROR(SMALL('Log Entries'!$A$1:$E$47,ROW('Log Entries'!A1)+COLUMNS('Log Entries'!$A$1:A1)*46-46),"")
to use a Macro in order to batch process a set of data that will have consistent columns, but may have additional rows.
I have attached the file, three tabs.
1st - Sch of Inv(2): The intended goal The data is sorted first by Georgraphic Location (A), then Strategy (C), then Substrategy (E). Column F should be the same formula that is currently there, but updated for column I once the information is pulled (I""/$F$29*100). The *100 is to only display the % symbol once. If there's a better way to calculate percentages and only show the symbol on the first one, that would also help. Column I is the place to calculate the data from Tab 2. Column K is your check figures, which are within $5 (due to a miskey probably, not important). Cell F29 will be a manually hard-coded number on each sheet (which would be entered before the Macro is initiated).
2nd - Cost Query: The data source sheet 6 columns, 5 have relevant data. Manager shortname is the 'primary key', but won't need to be displayed anywhere. Region-Strategy Code-Substrategy Name correspond to (A), (C) and (E) on Tab 1. The goal is to sum by each of these factors, then display the one sum on Tab 1. The pull will include column F, but it has no actual value. I'd prefer to be able to just keep it on Tab 2.
3rd - Pivot of Cost Query: The step inbetween? Here is the proof that the data can be easily pivoted to provide all of the values, but I'm having trouble figuring out how to quickly move those values into the predetermined format of Tab 1.
Questions: Is there something simple that I'm missing to move the data from the pivot into the predetermined format?
How can I use a macro to take the data on Tab 2, move it into a pivot on Tab 3, then move that data to the format (column I) in Tab 1? Is this process easier without the pivot step in the middle?
edit the below code? Having problem with the underlined portion. It is always showing the following error message - "Run-time error '1004' -Application-defined or object-defined error".
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
