Three-way Lookup Help Please

[Chris]

Hi, could someone please help me with the following?

I have done the following:

1. Worksheet named: Report has a dropdown list in cell B2 for a list of
Operations. The Operations are populated on worksheet named: Data in
column B.
I made a list of these operations on worksheet named: Operations and
gave this list a defined name = Operations!$A$2:$A$12. This was done so
that I could let the user click on drop-down lists in column B (on
worksheet
named: Data) to select an appropriate Operation.

2. Worksheet named: Report has a dropdown list in cell B4 for a list of
Groups. These Groups are populated on worksheet named: Data in column C.
I made a list of these groups on worksheet named: Groups and gave this
list a
defined name = Groups!$A$2:$A$29. This was done so that I could let the
user click on drop-down lists in column C (on worksheet named: Data) to
select an appropriate Group.

The following is an example of what I need:

For example: when the user clicks on the Report tab and then clicks on
cell B2 (drop-down list) to select an Operation (The Operations are
populated on worksheet
named: Data in column B), and then clicks on cell B4 (drop-down list) to
select a Group (The Groups are populated on worksheet named: Data in
column C),
I need some code or macro that will automatically lookup column X (on
the worksheet named: Data) and select all the cells in column X that
contain the text: "VACANT".

If there are any matching records (a record being one row of data on
worksheet named: Data) for these three criteria (Operation, Group and
cells in column X that contain the text: "VACANT"), then I need the code
or macro to copy the record (entire record from the worksheet named:
Data)
to a new workbook named: OMD Report.xls and paste it onto worksheet
named: Report (paste on cell A2). This new workbook is located on c:\
drive.

If anyone could please help with this, it would be greatly appreciated.

Kind regards,

Chris.

 

[Joel]

You can use Data - Autofilter to perform these operations manually or with a
macro. The two drop downlists should be on a different worksheet from the
DATA so the drop down boxes don't get filter by the autofiler.

Select the entire worksheet DATA. Then go to menu Data - Filter -
autofilter. You can then copy the visible cells using the menu

Edit - Goto - Special - visible Cells Only.

Then copy the cells to a new workbook.

If you record the macro while performing these operations I can easily make
a macro that will use the selections in drop down boxes. Let me know wherre
the new drop down boxes are located. the macro can go down each item of the
drop down box and create a new workbook for each selection.

 

[Patrick Molloy]

wasn't my code under your identical COUNTIF email substantially what you
need? send me your workbook directly- it might make it easier to
understand - and I'll send it back with the relevant code - it

I've added my original code.
basically you're filtering by operation and group AND where VACANT appears
in column X .. so i added this last bit

_____________
something like this maybe?
Sub GetData()
Dim index As Long
Dim rw As Range
With Worksheets("Summary")
.Range("B8:B11").ClearContent
For Each rw In Worksheets("S=data").Range("Data")
If rw.Range("B1") = .Range("B4") And _
rw.Range("C1") = .Range("B8") And _
rw.Range("x1") ="vacant" Then

.Range("B8").Offset(index) =
rw.Range("A1").Value
index = index +1
End If
Next
End With
End Sub

so this looks at each row in the data sheet and where the value in column B
matches the value in B4 and where the value in column C matches the value in
B8 then the value in column A is copies to the summary sheet.

Hi Patrick, Sorry I did not make this very clear. You are right, I do
have the formula but do not know how to put it all together to make this
work.

For example: when the user clicks on the Summary tab and then clicks on
cell B2 to select an Operation, and then clicks on cell B4 to select a
Group, I need some code or macro that will automatically populate cells:
B8:B11 & B25.

This populated data needs to be extracted from the worksheet named:
Data. I wanted to use the COUNTIF function to extract the values from
the worksheet named: Data into the cells B8:B11 & B25 on worksheet
named: Summary.

The Operations are populated on worksheet named: Data in column B. I
made a list of these operations on worksheet named: Operations and gave
this list a defined name = Operations!$A$2:$A$12. This was done so that
I could let the user click on drop-down lists in column B (on worksheet
named: Data) to select an appropriate Operation.

The Groups are populated on worksheet named: Data in column C. I made a
list of these groups on worksheet named: Groups and gave this list a
defined name = Groups!$A$2:$A$29. This was done so that I could let the
user click on drop-down lists in column C (on worksheet named: Data) to
select an appropriate Group.

I hope this is clearer - if not please let me know.

I really appreciate any help that I can get.

Cheers,

Chris.

___________

 

[Chris]

Hi Patrick, it may be easier if I send you a copy of my worksheet via
e-mail Would it be possible to please get your e-mail address so that i
can send you a copy of my workbook?

Kind regards,

Chris.

 

[Chris]

Hi Joel, thanks for your reply - much appreciated. At the moment
Patrick is helping me and I will hopefully send him a copy of my
spreadsheet to help clear things up. I have two pieces of code that I
need already posted to this forum that are related to my workbook.

Kind regards,

Chris.

 

[Patrick Molloy]

patrick_molloyATNOSPAMhotmailDOTcom