Extract Associated data (2000/2003)

Topic

New Reply

Hello All, I have attached a workbook with an example of my problem. If I select an employee name from the drop down, I need to list all other employees that are in the same location as the employee selected in the drop down, I need the employees names to be listed in the “P” column on sheet1. The emplyees and locations are listed on the employees sheet.
What would be my best approach to do this.
Thanks in advance.

Reply | Quote

Replies

You also shaded part of column Q. Do you need that to be populated too? If so, with what?

Reply | Quote

My mistake -I don’t need that-thanks

Reply | Quote

See attached version, it uses the Worksheet_Change event to run Advanced Filter with the Copy to another location option each time you select an employee in cell A3.
To do this, I changed the column header in column P to match that in column A, and set up a criteria range in G1:G2 on the Employees sheet.

Reply | Quote

This does what I need. On my actual worksheet I’m nt sure how to insert the code. Do I have to make a special module? What is BLAD1?

Reply | Quote

To create the code, right-click the worksheet tab of Sheet1, and select View Code from the popup menu. This will open the worksheet module, and that’s where you create worksheet event code.

Blad1 is the Dutch name for Sheet1. Apparently Excel changed the code name for the sheet when I saved the workbook in my Dutch language version. You don’t have to do anything with it, you can stil use Sheet1 and Employees.

Reply | Quote

OK, I hate to be a nuisance. When I right Click on sheet1 and click view code, there is another macro that is in my original worksheet. This should be a blank area, correct? How do I get a blank page?

Reply | Quote

Apparently you already had code there. That need not be a problem. What was the name of the procedure?

Reply | Quote

Sub SaveFile() is the code that comes up in my original workbook.

Reply | Quote

And what is the title (caption) of the module window?

Reply | Quote

(No Text)

Reply | Quote

Thanks, but that is not what I asked. I’d like to know the window title. If the module is maximized, the title will be in the title bar of the Visual Basic Editor, otherwise it will be in the title bar of the module window.

Reply | Quote

Sorry.
2007 Incident Assistant.xls-[sheet1 (Code)]

Reply | Quote

OK, that’s what I wanted to know. Unless that module contains other code already, the SaveFile macro should not be in there, but in a standard module, i.e. the kind of module you create by selecting Insert | Module in the Visual Basic Editor. The worksheet module should only be used for event procedures such as Worksheet_Change.

Reply | Quote

OK, I deleted it and copied it to a module. Then inserted your code. Do I need to do anything else, or should the event change work automatically? I set up everything else as you instructed, but it doesn’t print out on sheet 1
Thanks for your patience and concern with my problem

Reply | Quote

Make sure you set up both sheets as in the workbook I posted. The code should then work automatically.

Reply | Quote

Yes, I have it set up with the vlookup and the range changes. I notice though, in the name box on your sheet it list “Extract” in my original sheet it doesn’t. I must have not done something right. Thanks again

Reply | Quote

In the Visual Basic Editor, click in the line

Private Sub Worksheet_Change(ByVal Target As Range)

Press F9. This will set a so-called breakpoint (you can remove it later on by clicking in the same line and pressing F9 again).
Switch to Excel, and select a different employee in the dropdown list in cell A3.
The Visual Basic Editor should be activated, with the above line highlighted in yellow.
You can step through the code by repeatedly pressing F8.
Does every line of the code get executed, or do you skip over lines?

Reply | Quote

I am now getting a syntax error. Notice that I have changed the ranges here to reflect my original sheet ranges. my vlookup formula is in U2 instead of G2

Reply | Quote

You need a “line continuation character” after the advanced filter

This is a space followed by an underscore ( _) like you have after the other lines

Steve

Reply | Quote

Thanks . But I still get the same error. The line that starts with Action:=xlFilterCopy, through the next 2lines are highlighted in blue

Reply | Quote

Did you remember to put a space before the underscore?

If that doesn’t help, delete the entire procedure, copy the procedure from the workbook I attached and paste it into your worksheet module, then modify the ranges as needed.

Reply | Quote

I did this, on your sheet and my original sheet, The line doesn’t turn yellow. On the sheet you posted it went through as you said it should. Of course your sheet is working.

Reply | Quote

Perhaps you copied the code into the wrong module. It must be in the module behind the sheet with the cell with the dropdown arrow in which the user selects an employee.

Reply | Quote

I think it’s in the right place see below. I’m wondering about, when I drop down the name box in your sheet I see “Extract” but in mine it’s not there. Did I not set up something properly in the workbook?

Reply | Quote

It appears to be in the correct place.

The name Extract should be created automatically by Excel when it executes the advanced filter.

Reply | Quote

Thanks again for all your help.

Reply | Quote

Did you get it to work now?

Reply | Quote

No, it’s still giving the syntax error that is highlighted in yellow in my previous reply.

Reply | Quote

I sent you my e-mail address by PM (at your request). You can mail the original file if you wish.

Reply | Quote

There are two problems, one of which I should have seen in your screenshot.

1) The range to filter should include the column headers, so the filter instruction should have A1:O1159 instead of A2:O1159:

Worksheets(“Employees”).Range(“A1:O1159”).AdvancedFilter _
…

2) The Extract to range should use the exact same column header as you want to extract from the data range. Since the employees’ names are in a column named Name, cell P1 on Sheet1 should also contain Name

With these two modifications, the code works correctly for me.

Reply | Quote

Worked like a charm. Thanks soooo much. This is a big help.

Reply | Quote

One LAST question / In the change event
– How do you insert code to unprotect the sheet, (no passwords)
-then the change event executes
– then reprotect the sheet,(no passwords)

Reply | Quote

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(“N3”), Target) Is Nothing Then
Application.EnableEvents = False
Me.Unprotect
Worksheets(“Employees”).Range(“A1:O1159”).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Worksheets(“Employees”).Range(“U1:U2”), _
CopyToRange:=Range(“P1”)
Me.Protect
Application.EnableEvents = True
End If
End Sub

Reply | Quote

Thankyou. That worked perfect.

Reply | Quote