Foe eg: Sheet2 contains:

Project# Phase Sp# Details status Manager ...
1234 1-0110 N80 abc Test am
1234 1-0210 N97 def Incom bd
1234 2-0210 N45 lmn Test cf
1234 1-0110 N67 jkl Com er


Sheet1 contains:

Project# Phase SP# Status Manager
1234 1-0110

The above 2 values are inserted by the user:
Now: On running the macro:

the sheet 1 should be populated with all the records for phase 1-0110
Desired output:

Project# Phase Sp# Status Manager
1234 1-0110 N80 Test am
N65 Com er

Any help would be greatly appreciated!!!
Thanks

How about a PivotTable or Advanced Filter instead?

--JP

On Jan 19, 2:49*pm, anshu minocha wrote:
Foe eg: Sheet2 contains:

Project# * Phase * Sp# * Details status Manager ...
1234 * * * *1-0110 *N80 * abc * * * Test * *am
1234 * * * *1-0210 *N97 * def * * * *Incom *bd
1234 * * * *2-0210 *N45 * lmn * * * Test * *cf
1234 * * * *1-0110 *N67 * jkl * * * * Com * *er

Sheet1 contains:

Project# *Phase * SP# * Status *Manager
1234 * * * 1-0110

The above 2 values are inserted by the user:
Now: On running the macro:

the sheet 1 should be populated with all the records for phase 1-0110
Desired output:

Project# Phase *Sp# *Status Manager
1234 * * *1-0110 *N80 *Test * *am
* * * * * * * * * * * * *N65 *Com * *er

Any help would be greatly appreciated!!!
Thanks

Hey JP,

But the requirement is to obtain the values on a single
click.So was thing of writing a macro:
If atleast the macro can Populate all the SP# for the particular phase

Sheet1:

Project# Phase SP#
1234 1-0110 N80
N65
Is it possible to get all the SPId's getting populated for phase
1-0110 from sheet2 using a macro
Please advise
Thanks

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"anshu minocha" wrote in message
...
Hey JP,

But the requirement is to obtain the values on a single
click.So was thing of writing a macro:
If atleast the macro can Populate all the SP# for the particular phase

Sheet1:

Project# Phase SP#
1234 1-0110 N80
N65
Is it possible to get all the SPId's getting populated for phase
1-0110 from sheet2 using a macro
Please advise
Thanks

You can create PivotTables and Advanced Filters using VBA. If only you
would do a search to find out how to use those tools.

You could also put the data into Access and create a parameter query
that asks you for the Project# and Phase, and returns a table with the
matching records.

--JP

On Jan 20, 1:49*pm, anshu minocha wrote:
Hey JP,

* * * * * *But the requirement is to obtain the values on a single
click.So was thing of writing a macro:
If atleast the macro can Populate all the SP# for the particular phase

Sheet1:

Project# * *Phase * * SP#
1234 * * * * 1-0110 * * N80
* * * * * * * * * * * * * * * N65
Is it possible to get all the SPId's getting populated for phase
1-0110 from sheet2 using a macro
Please advise
Thanks

Hi Anshu,

Give this a test, you will have to change the ranges in the code to suit
your sheet or set up an example on your sheet to match the code ranges.
Can be tweeked to suit of course.

Sub TheProjects()
Dim i As Long
Dim j As Variant
Dim Pro As Range
Dim Pha As Range
Dim cell As Range

i = Range("I9").Value ' Type Project number in I9
j = Range("J9").Value ' Type Phase number in J9
Set Pro = Range("B9:B12") ' List of Project# range
Set Pha = Range("C9:C12") ' List of Phase numbers range

'Copies the Pro and Pha number into the next available row in Column I and J
Range("I9:J9").Copy Range("K100").End(xlUp).Offset(1, -2).Resize(1, 2)

'Gathers the info for Pro range and Pha range and copies to the
'next available row in column K
For Each cell In Pro
If cell.Value = i And cell.Offset(0, 1).Value = j Then
cell.Offset(0, 2).Resize(1, 4).Copy
Range("K100").End(xlUp).Offset(1, 0)
End If
Next
End Sub

HTH
Regards,
Howard

"anshu minocha" wrote in message
...
Foe eg: Sheet2 contains:

Project# Phase Sp# Details status Manager ...
1234 1-0110 N80 abc Test am
1234 1-0210 N97 def Incom bd
1234 2-0210 N45 lmn Test cf
1234 1-0110 N67 jkl Com er


Sheet1 contains:

Project# Phase SP# Status Manager
1234 1-0110

The above 2 values are inserted by the user:
Now: On running the macro:

the sheet 1 should be populated with all the records for phase 1-0110
Desired output:

Project# Phase Sp# Status Manager
1234 1-0110 N80 Test am
N65 Com er

Any help would be greatly appreciated!!!
Thanks

You did not follow my instructions
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"anshu minocha" wrote in message
...
Hey JP,

But the requirement is to obtain the values on a single
click.So was thing of writing a macro:
If atleast the macro can Populate all the SP# for the particular phase

Sheet1:

Project# Phase SP#
1234 1-0110 N80
N65
Is it possible to get all the SPId's getting populated for phase
1-0110 from sheet2 using a macro
Please advise
Thanks

Hello Don,
I did send it on your gmail id.
Thanks

I did NOT get it and if you can't do it in the manner I request, I will not
be able to assist. Follow these instructions.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"anshu minocha" wrote in message
...
Hello Don,
I did send it on your gmail id.
Thanks

Thankyou I happened to figure out the problem.
Thanks JP and Don

Thankyou Howard