Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Dynamic list sorting Results

I've posted a link in a the 'charting forum', but it's more of a general question stemming from a charting issue. Link to other forum is below for maybe more detail?

I've created a dynamic link called 'namelist' and it uses offset to dynamically change the size of the list as items are added or subtracted:

=OFFSET(sheet1!$a$1,0,0,COUNTA(sheet1!$a:$a),1)

Now I have a second sheet ("sheet2") that is very similar, but may have more or less entries than sheet1. I'd like to have this exact same dynamic list function for "sheet2", but the offset command references a SPECIFIC sheet, which really doesn't allow me to do what I want... is there a way around this?

http://www.excelforum.com/excel-charting/602018-dynamic-chart.html

First off, let me say thanks to the Ozgrid community that has helped me over and over through these forums. You are phenomenal.

Question 1: I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..)

Detail:
I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employees. I've been able to successfully define named ranges to work with these as dynamic lists. I can append names, or delete names, and the Chicago, or Seattle boxes (drop downs created with data validation formulas inside named ranges) will reflect the updated names correctly. I have been unable to make a single drop down with all the names from both offices, that is updated dynamically. I've attached a sheet so that this is easier to understand. Basically I need the "BOTH" drop down to actually work.

The Chicago range is defined as:
=OFFSET(Sheet1!$A$2:$A$11,0,0,COUNTA(Sheet1!$A$2:$A$11),1)
The Seattle range is defined as:
=OFFSET(Sheet1!$B$2:$B$11,0,0,COUNTA(Sheet1!$B$2:$B$11),1)

In the boxes on the right of the lists, I just have a Data Validation List formula as:
=Chicago and the other as =Seattle

Notes: I've noticed that if there are spaces in the list then it does not display correctly. I've wondered if there is different way to write the formula to make it take into account spaces in the middle of the list, to make it more user friendly.

Thanks for any ideas you have, or any help you can provide. If the attached sheet doesn't work for you, please access it here.

Thanks,
John

Hi All,

MY IDEA: I am looking to populate a specific column of cells on "Worksheet 1" with the data stored in a specific row of cells in "Worksheet 2", all based on a value selected from a "Dynamic list" in "Worksheet 1". The list would be based on the “unique identifiers” for each row in “Worksheet 2”.

MY ISSUE: I am unsure as to how to approach this problem as there are many variables. Specifically the Dynamic list and the data stored in "Worksheet 2". I am assuming that I can create some sort of "change event" for the Dynamic list, so when a user makes a selection the event will look at the row called in the list; find the range of data in that row, then copy the range of data to the specific column of cells in "Worksheet 1".

Unfortunately there will be 50+ rows of data that are at least 112 columns long. The event|macro|module will have to be able to look at over 50 different ranges of data each time a row is selected from the Dynamic list, which seems daunting to me. I assume I would have to write 50 or more ranges in order to accomplish my task.

All ideas are welcomed and appreciated.

Jeff

Hi there again,

Having sorted out the Skills Audit form to use a dynamic list and vlookup to allow a paramedic to select a drug code in one cell and the adjacent cell would display the drug name, I was asked if I could reverse this function.

That is, the selection is the Drug and the vlookup would be the drug code.

Yet, while the correct drug would be shown with whatever drug code was selected, I can't get the selection to work in reverse.

http://www.ziddu.com/download/171752...atev6.xls.html is a copy of the sheet that doesn't work while selecting drug therapy. http://www.ziddu.com/download/171761...atev1.xls.html is working with selection of drug codes.

An example would be Drug Code: 4 Drug Therapy: Glucose 10%

Selecting 4 in v1 sheet gives Glucose 10%
Selecting Glucose 10% in v6 gives you 10 yet the same vlookup and Name Define method is used.

If someone would be kind enough to review the above sheets and point me in the right direction, I would be very grateful.

Kind regards,

Guy

.

Make a dynamic alphabetically sorted UNIQUE list

Is it possible to generate a unique list of data from a given column on SHEET1 to a different sheet, SHEET2 and sort it alphabetically with a dynamic formula?

I have SHEET1

In the first column I keep adding data every week (approximate 8000- 9000 records)

Tom
Henry
Robert
Jack
David
David
Tom
Henry
Jack

In SHEET2 I need to generate a list of all the unique records in column A from SHEET1 sorted alphabetically

David
Henry
Jack
Robert
Tom

I would love to know if this is possible WITHOUT USING A MACRO

Because the comp I need to use this sheet on either will have LOW SECURITY by default or this will have to run in STAR OFFICE which usually screws up excel macros

But if not possible I will accept a macro

The unique list must be auto updated the moment new data is added to SHEET1

Is this possible???

Regards,
Hyperdreamz

.

Morning all

I was wondering if someone would be kind enough to provide me with a solution to my problem.

Also posted here:

http://www.mrexcel.com/forum/showthr...t+dynamic+list

I have a workbook which contains several worksheets (please see attached file as an example).

There is an ENTRY worksheet, which contains a list, which the user uses to enter details of a new product. The user adds the product to the end of the list already there. One field which must be filled is the "Status" field, where the user selects whether the product is either on sale, or sold-out.

The other worksheets (ON SALE and SOLD-OUT) display the list of the relevant materials from the ENTRY sheet, i.e. a list of all materials sold-out, or all materials on sale, but these worksheets must be sorted in alphabetic order.

Basically, the ENTRY sheet contains all materials unordered, and the other sheets contain only relevant materials ordered.

The sort must be by material ID. It must be immediate (i.e. no need to "refresh" the workbook), on click of ON SALE or SOLD-OUT.

Can someone please help me with a macro for this?

Thanks in advance.

Alis

First off, let me say thanks to the Exceltip community that has helped me over and over through these forums. You are phenomenal.

Question 1: I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..)

Detail:
I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employees. I've been able to successfully define named ranges to work with these as dynamic lists. I can append names, or delete names, and the Chicago, or Seattle boxes (drop downs created with data validation formulas inside named ranges) will reflect the updated names correctly. I have been unable to make a single drop down with all the names from both offices, that is updated dynamically. I've attached a sheet so that this is easier to understand. Basically I need the "BOTH" drop down to actually work.

The Chicago range is defined as:
=OFFSET(Sheet1!$A$2:$A$11,0,0,COUNTA(Sheet1!$A$2:$A$11),1)
The Seattle range is defined as:
=OFFSET(Sheet1!$B$2:$B$11,0,0,COUNTA(Sheet1!$B$2:$B$11),1)

In the boxes on the right of the lists, I just have a Data Validation List formula as:
=Chicago and the other as =Seattle

Notes: I've noticed that if there are spaces in the list then it does not display correctly. I've wondered if there is different way to write the formula to make it take into account spaces in the middle of the list, to make it more user friendly.

Question 2: Once the first piece is complete, I'll be in good shape. Really, Question 1 is the big one. I did however want this merged list to also be sorted alphabetically. I thought, oh, I'll just wrap a SORT() function around the working formula, and bang I'll have it alphabetized. Then I realized that Excel does not have a SORT() function. How might I sort this merged list, so that it appears alphabetically in the drop down list?

Thanks for any ideas you have, or any help you can provide. If the attached sheet doesn't work for you, please access it here.

Thanks,
John

Hi,

I have read a few threads on here on how to remove blanks and errors from a dynamic named range list, however I can't get it to work as intended.

I want to run a fantasy rugby league with a few work friends. So I have three sheets 'Squad', 'Players' & 'Data'.

The players sheet contains a master list of all the players, the squad sheet is where my work friends will choose their team and the data sheet contains a couple of formulas to dynamically sort the players from the players sheet into various groups that will be used as lists on the squad page.

I am using a formula to count the position and bring back a list of players in a certain positiom which is spread across three rows:

=MATCH($A$2,Players!$B:$B,0)
=MATCH($A$2,Players!$B:$B)
=1+A4-A3

I then use the following formula to give each of the results a sorting number:

=COUNTIF($C:$C,"

Hi everyone,

I'm having some trouble coming up with a formula. I have a giant list of books in sheet1, which contains a number of columns: date, book name, language etc

I then have a number of tabs for different languages (English, French, German etc)

I would like to have formulas in the individual tabs that reference sheet1, but only include books in that language. So the French tab would only list the books from Sheet1 that are in French. The formula would have to be dynamic to account for new additions, and ignore new additions that are added for other languages.

I'm assuming this would have to be some sort of MATCH/IF array formula, but I can't work it out.

Thanks for your help.
Vic.

Hi

So I am working on a file which needs to have one drop down box dependant on another (this is not my issue).

So I have one drop down box for Location and the second is for the type of roles within that location. The issue I have is that I have a master list of roles (around 40 different master roles) and each of these is potentially called something different for each location, but not all locations have all roles (listed as Invalid if role does not exist in location).

I would like to find a way of creating a dynamic list of roles based on the location but not display the roles listed as Invalid (see attached example). I ideally do not want to run a macro to do this as I will need to send the spreadsheet out to non savy users who may need to update the role table (which needs to stay in the format similar to to that of the Roles Tab).

Does any one have any ideas what sort of formula I can use to get the results to dynamically update the Role drop down on the Data tab so it lists all roles apart from those which are listed as Invalid (I have looked at using and array but it gives me #N/A items which show up in the drop down for roles).

Thanks in advance for any help.Excel Problem.xlsx

Hi Guys,

Not sure if this is the place to post the thread but i need help writing a macro. Say i have an .xls file that contains new connections for a whole list of customer, i need to slit the file into separate workbooks based on the customers name. Sheet 1 has the Data i need to split and Sheet 2 has the Names i need it to sort through when creating the new files. Here is the file. This is done on a monthly basis and right now it takes hours per file.Connections.xls[TD="class: xl63, width: 463"]
[/TD]
[TD="class: xl63, width: 97"][/TD]
[TD="class: xl63, width: 97"][/TD]
[TD="class: xl63, width: 97"][/TD]
[TD="class: xl63, width: 97"][/TD]
[TD="class: xl63, width: 97"][/TD]
[TD="class: xl63, width: 97"][/TD]
[TD="class: xl63, width: 97"][/TD]
[TD="class: xl63, width: 97"][/TD]

I have tried all suggestions in the following posts without sucess (all items are listed, but only unique ones are wanted).

Add Unique List To ComboBox
Getting Unique Values Into A Combo Box

I'm not sure if it's because I'm using a dynamic range. The range I am using (OutKeyNumber) is numbers with a source code

	VB:
	
OutKeyNumber = OFFSET(CheckedOut!$B$2,0,0,COUNTA(CheckedOut!$B:$B)-1,1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is what I have tried to do, but it doesn't skip any duplicate numbers. The combobox is called Key_Box


	VB:
	
 UserForm_Initialize() 
     
     
    Dim v As Object, d, aItems, i, akeys 
    Set d = CreateObject("Scripting.Dictionary") 
    With d 
        .CompareMode = vbTextCompare 
        For Each v In Worksheets("CheckedOut").Range("OutKeyNumber") 
            If Not IsEmpty(v.Value) And Not .exists(v.Value) Then 
                .Add v, Nothing 
                Me.Key_Box.AddItem v 
            End If 
        Next v 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Auto Merged Post Until 24 Hrs Passes;

It seems that this will work, however, it is quite long, but it works.


	VB:
	
 UserForm_Initialize() 
     
     
     'Dim v As Object, d, aItems, i, akeys
     'Set d = CreateObject("Scripting.Dictionary")
     'With d
     '    .CompareMode = vbTextCompare
     '    For Each v In Worksheets("CheckedOut").Range("OutKeyNumber")
     '        If Not IsEmpty(v.Value) And Not .exists(v.Value) Then
     '            .Add v, Nothing
     '            Me.Key_Box.AddItem v
     '        End If
     '    Next v
     'End With
     
    Dim rListSort As Range, rOldList As Range 
    Dim strRowSource As String 
     
     'Clear Hidden sheet Column A ready for list
    Worksheets("CheckedOut").Range("E1", Worksheets("CheckedOut").Range("E65536").End(xlUp)).Clear 
     
     
     'Set range variable to list we want
    Set rOldList = Worksheets("CheckedOut").Range("OutKeyNumber") 
     
     'Use AdvancedFilter to copy the list to Column A _
    of the hidden sheet And remove all dupes 
    rOldList.AdvancedFilter Action:=xlFilterCopy, _ 
    CopyToRange:=Worksheets("CheckedOut").Cells(1, 5), Unique:=True 
     
     'Set range variable to the new non dupe list
    Set rListSort = Worksheets("CheckedOut").Range("E1", Worksheets("CheckedOut").Range("E65536").End(xlUp)) 
     
    With rListSort 
         'Sort the new non dupe list
        .Sort Key1:=.Cells(5, 1), Order1:=xlAscending, Header:=xlNo, _ 
        MatchCase:=False, Orientation:=xlTopToBottom 
    End With 
     
     'Parse the address of the sorted unique items
    strRowSource = Worksheets("CheckedOut").Name & "!" & Worksheets("CheckedOut").Range _ 
    ("E1", Worksheets("CheckedOut").Range("E65536").End(xlUp)).Address 
     
    With Me.Key_Box 
        .RowSource = vbNullString 
        .RowSource = strRowSource 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am setting up some code to collect strings of instructions (we call them Routing Operations) from cells on a worksheet. The code gathers up the instructions and populates a user form with them so that an engineer can edit them. I'm doing it form-based so that I can control the format and layout of the text entered, as it will later be used in other operations (document merge and copy/paste to other applications) and needs to be consistent. The instructions are divided into Headings for each group in the production department. Each heading has 1 or more instructions under it (see example below)

MECHANICAL: 1. DIMENSIONAL DRAWING: 4321 2. ASSEMBLY DRAWING: 1234 ELECTRICAL: 1. WIRE SYSTEM FOR 460/3/60 2. EQUIP SYSTEM WITH FLEXIBLE CONDUIT TESTING & SETTINGS: 1. REFERENCE APPLICABLE ENGINEERING STANDARDS. NAMEPLATE: 1. REFERENCE APPLICABLE ENGINEERING STANDARDS.

PAINT: 1. REFERENCE APPLICABLE ENGINEERING STANDARDS. CRATING & SHIPPING: 1. REFERENCE APPLICABLE ENGINEERING STANDARDS. SHIP LOOSE ITEMS: 1. N/A The above is an example, as different products may have the headings in a different order (an electrical panel goes through the electrical shop first, so the Electrical and Mechanical headings are switched). Currently, I'm using 7 Arrays to store the text lines, and since the groups are spaced out by a blank cell on the sheet, I can use that as a marker to switch to the next array. My code to collect the instructions is below:


	VB:
	
 
 'Routing instructions begin on row 70
intCuRow = 70 
 
 ' set initial values for headers
strNextHeader = UCase(ActiveSheet.Cells(intCuRow, "AN").Value) 
 
 ' first header will be used later when the form is created and sorted
strFirstHeader = strNextHeader 
 
 ' loop through routing instructions and collect lines into arrays
For intCount = 1 To 7 
    strCurHeader = strNextHeader 
    Redim strActiveRouting(0 To 0) 
    intRtLine = 0 
    Do While strCurHeader = strNextHeader 
        If IsEmpty(ActiveSheet.Cells(intCuRow, "AN")) = False Then 
            Redim Preserve strActiveRouting(0 To intRtLine) 
            strActiveRouting(intRtLine) = UCase(ActiveSheet.Cells(intCuRow, "AN").Value) 
             ' Check the length of the string and set the box width to the longest length required
            If (Len(ActiveSheet.Cells(intCuRow, "AN").Value) * intCharWidth) > intBoxWidth Then 
                intBoxWidth = Len(ActiveSheet.Cells(intCuRow, "AN").Value) * intCharWidth 
            End If 
            intRtLine = intRtLine + 1 
        End If 
        intCuRow = intCuRow + 1 
        If IsEmpty(ActiveSheet.Cells(intCuRow, "AN")) = True Then 
            intCuRow = intCuRow + 1 
            strNextHeader = UCase(ActiveSheet.Cells(intCuRow, "AN").Value) 
        End If 
    Loop 
     
     ' assign active routing to a holding array
    Select Case intCount 
    Case 1 
        strRouting1() = strActiveRouting() 
    Case 2 
        strRouting2() = strActiveRouting() 
    Case 3 
        strRouting3() = strActiveRouting() 
    Case 4 
        strRouting4() = strActiveRouting() 
    Case 5 
        strRouting5() = strActiveRouting() 
    Case 6 
        strRouting6() = strActiveRouting() 
    Case 7 
        strRouting7() = strActiveRouting() 
    Case Else 
        MsgBox "something went wrong", vbCritical 
    End Select 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'd like to be able to make this operation totally dynamic - so I can collect any number of headings, with any number of instructions underneath them. So if there's only 5 headers, the form can handle that, and if there's 8 or 9 it will handle that too. This way if our standards change, I don't need to modify the code. It should also make populating the user form easier, since the order will already be defined as the subroutine collects the strings.

I looked into collections but couldn't figure that out. I also looked at multi-dimensional arrays, but couldn't find anything clear on that topic. Multi-dimensional Arrays led to nested arrays, which (again) I couldn't find a clear explanation on. My current thought is to use one big array, and insert some kind of "flag" to an element whenever a blank line is encountered, but I'm hoping to find a more elegant solution...

Thanks in advance for the advice!

Hello All,

I'm trying to create a worksheet that has a database with the capability to
dynamically set each of the three sort keys. My database is in the range
from a5:g15, the field names are in row 5. I've created in cell drop down
lists using validation in cells a4, b4, c4. I'd like to use these dropdowns
to select sort keys 1-3 respectively.

Searching google I've found some posts that have something similar with a
single sort key that was posted by Steve Bell:

Dim x as Integer

x = Worksheetfunction.Match(Range("A3"),Range("A5:g15" ), 0)

Selection.Sort Key1:=Columns(x), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

but I can't figure out how to modify it to accomodate three sort keys. I've
tried dimensioning two other variables y and z for the other sort keys and
the pasting the above code with x changed to y and z respectively....no
luck, I keep getting errors.

This is what I have so far.

Dim x as Integer
Dim y as Integer
Dim z as Integer

x = Worksheetfunction.Match(Range("A3"),Range("A5:g15" ), 0)
y = Worksheetfunction.Match(Range("b3"),Range("A5:g15" ), 0)
z = Worksheetfunction.Match(Range("c3"),Range("A5:g15" ), 0)

Selection.Sort Key1:=Columns(x), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort Key2:=Columns(y), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort Key3:=Columns(z), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Could anyone possibly help me figure out the proper syntax?...

Thanks for your help in advance.

Will

HOWDY ALL, i REALLY APPRECIATE ALL THE HELP YOU PROVIDE.
I NEED TO LEARN HOW TO CONTROL THE NUMBER OF CELLS THAT WILL DISPLAY THE
DROPDOWN LIST IN A DYNAMIC RANGE. CURRENTLY:

I HAVE A NICE WORKBOOK SET UP BY THE YEAR OF AN AUTO CONTAINING TWO
WORKSHEETS, THAT REPRESENT EACH AUTO BY A SPECIAL NUMBER. EACH AUTOS SHEET
CONTAINS DESCREPENCIES FOUND WITH THE VEHICLE; EACH DESCREPENCY GETS IT'S OWN
SPECIAL NUMBER.
CURRENTLY I AM SET UP WITH A DYNAMIC RANGE DROP DOWN FOR DESCREPENCIES, AN
AUTO DATE INSERT, A NAMED RANGE DROP DOWN FOR INTIALS AND A SEQUENTIAL NUMBER
WORKSHEET IN ANOTHER WORKBOOK THAT IS HYPERLINKED TO THE COLUMN WHERE THE NEW
DESCREPENCY NUMBER GOES. HERE'S THE CODE:

IN MODULE 1 I HAVE:

Option Explicit

Sub Workbook_Open()
Columns("B:B").Select
Range("B3").Activate
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="WAIVER%20NO.xls", _
TextToDisplay:=""
End If
Else
End If
End Sub

NO SHEET 1OR2

IN SHEET 3 (LISTS)I HAVE:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

IN SHEET 4 (86x36236; an auto number)
I HAVE:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row > 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Na meList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
End Sub

I DID INSTALL A COMBO BOX AND SET THE ROWS BUT IT HAD NO EFFECT??

SO, AGAIN, i NEED TO LEARN HOW TO CONTROL THE NUMBER OF ROWS THAT WILL
DISPLAY THE DYNAMIC LIST; CURRENTLY IT WILL ONLY SHOW THE DROP DOWN UP TO ROW
17, AFTER THAT IT WILL NOT DISPLAY THE DROPDOWN. i CANNOT FIND WHERE THIS IS
DELINEATED, i SUSPECT MAYBE IT IS HELD IN MEMORY? SURE WOULD APPRECIATE SOME
HELP. i YOU CAN'T REPLY BY 4:30 CST, MY EMAIL IS

I want to be able to enter a Post Code (Zip Code) in a cell and then using
the Vlookup function find the first occurence of that in the post code list
(sorted by code), then find the last occurence, and use these two values as
the start end end points for a data validation list so that the user can
select the desired location from the list of locations having the same post
code.

Hello,
I am trying to select a range of cells using a macro. The range grows each
day starting in cell B5 and ending in H* (* = the row with the last day's
worth of data). Two lines down, there is a grand total line, and two rows
from that is a footer of sorts, with date and a page number.

I found a way to get to the last used cell using a macro, however I do not
want the last cell. I want the last cell minus 4 rows.

Also, I am not sure how to select the entire range from B5 to H*. I am able
to select one cell or the other, but not the entire range.

I only dabble in VBA and have not had any program training. Any help that
anyone can provide will be greatly appreciated!

Let me know if you need more details.

Thanks in advance!

Excel 2002 SP3:

I have a workbook which lists our member's various fee payments. Each worksheet contains data for a given year sorted by date and member name(sort order mustn't be changed). There are numerous and varied fees paid by each member during a year, and new members are constantly added. I need to create a list of all the members, the number of fees they paid, and the total amount of fees paid for each year.

I don't have any problem with the counting and summing, however, I'm not sure how to automatically generate a dynamic list of the members in Excel. (Yes, I know this would be a cinch in Access or some other DB, but that's not an option available to me in this case -- "@&$#*!, how I hate politics"). Anyone got a method for doing this?

- Thanks,
Jamie

I have a dynamic list that can have data added/removed from it by the user. I have got the "adding" part sorted out okay but am having some trouble with the "removing" part. Here is an example of what my list looks like:

Name
-----
Tracy
Bob
Anna
Roger
So the list is not in alphetical order. What I want to do is be able to remove any item from the list e.g. remove "Anna":

Name
-----
Tracy
Bob

Roger
And then to move all entries below where "Anna" was in order to remove the blank space e.g.

Name
-----
Tracy
Bob
Roger
However I do not want to sort the list alphabetically. I want the names to stay in the exact same order.

Does anyone have any suggestions as to how I can achieve this via a VBA macro?

Cheers
-Rob

Ps - I should also add that I don't want to do this by deleting the rows that have the blank cells. Ideally I'd just like to move all the data beneath the blank cell up by one.

Hi all,
I would appreciate a bit of help with thsi. I got some code for a dynamic
list from Contextures and it works great except it wants to sort my list for
me which gums everything up.
I have a workbook with two sheets, one is Lists and one is Input

In the lists I want to have info that is set into categories i.e. "Front of
vehicle", Rear of vehicle. the input drop down will show this and the user
selectw what they want or adds a new item. I do not want the new item to be
pushed somewhere inside a sorted list, rather it should be added to the end
of the pre-determined list I have created.

the code is as follows:
Lists sheet:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Th Input (data validation)
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row > 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub

Please help me figure out how to keep the dynamic functionality but lose the
sorting so my lists stays as I created it. Thanks, larry


No luck finding an answer? You could always try Google.