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.
"Chris" wrote in message
...
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.
*** Sent via Developersdex http://www.developersdex.com ***
___________
"Chris" wrote in message
...
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.
*** Sent via Developersdex http://www.developersdex.com ***