Pull info from separate worksheet based on given criteria

[Chas]

I have 2 worksheets. The first has a list of all projects and the second
needs to pull projects based on certain criteria.

Worksheet #1

Project % $'s Date
Proj #1 85% $$$ 10/15/08
Proj #2 17% $$$ 11/07/08
Proj #3 62% $$$ 09/29/08
Proj #4 99% $$$ 08/15/08
Proj #5 47% $$$ 12/15/08
Proj #6 89% $$$ 10/15/08
Proj #7 70% $$$ 10/15/08

From this worksheet i want to pull only those projects with a % higher than
a given critieria.

I would prefer to do this with a formula as result needs to be live.

Any help that can be given is greatly appreciated.

Thanks

 

[dlw]

vlookup formula using the !sheetname referencing will work

 

[Chas]

Unfortunately the projects won't be nicely organized the way my example was.
I believe V-lookup needs the info to be sorted by alphabetically and our
projects will never be sorted that way.

 

[Chas]

I should also note that i would prefer a nice clean output without blank
lines in between the info which i why i can't use an If or And formula.

Thanks
again

 

[Dave Peterson]

Your data doesn't need to be sorted for =vlookup() to work.

Since you want an exact match (right?) based on project #, then you should make
sure you use the 4th parm (0 or false):

=vlookup(a1,sheet2!a:e,2,false)

That 4th parm = 0 or false means you want an exact match.

 

[Chas]

My apologies for the confusion. I don't think I explained my worksheets very
well. I need a formula for worksheet 2 that will actually pull the project
title from Worksheet 1 if certain criteria is met. So if none of the
projects meet the criteria, then worksheet 2 would actually be empty except
for the formulas.

Thanks again!

 

[Dave Peterson]

What's the criteria that you type into worksheet?

If it's the project id, you could use =vlookup().

If you don't type in any criteria, what would the formula be based on?

 

[Chas]

the criteria is based on the percentage. I want to pull only the projects
that have a percentage greater than or equal 75% (number will change, formula
will reference a cell that contains this information). I can get several
different formulas to work on the first line, but none work when copy down or
when the the % is less than the criteria or I end up with the same result on
every line.


In the example given below, the result on Worksheet #2 would be:
Project % $'s Date
Proj #1 85% $$$ 10/15/08
Proj #4 99% $$$ 08/15/08
Proj #6 89% $$$ 10/15/08

Thanks again so much for taking the time to assist me with this.

 

[Dave Peterson]

I would filter the data and copy the visible rows to the other location.

But someone else may chime in with a formula approach.

the criteria is based on the percentage. I want to pull only the projects
that have a percentage greater than or equal 75% (number will change, formula
will reference a cell that contains this information). I can get several
different formulas to work on the first line, but none work when copy down or
when the the % is less than the criteria or I end up with the same result on
every line.

In the example given below, the result on Worksheet #2 would be:
Project % $'s Date
Proj #1 85% $$$ 10/15/08
Proj #4 99% $$$ 08/15/08
Proj #6 89% $$$ 10/15/08

Thanks again so much for taking the time to assist me with this.

 

[Chas]

Thanks for your help.

The problem with the filter is that it's a shared workbook and for the
advanced filter, you have to unshare the workbook.

Does anyone know if this would be possible to do with a macro? A basic
looping if then statement.

I don't know how to compose macros but the general statement would be select
tab "Active B&P", if cell "C2" is greater than or equal to Tab "Labor $s"
cell "B1" then paste corresponding cell from "Active B&P" to the next empty
cell in column a on "Labor $'s"


Thanks again for the help!

 

[Dave Peterson]

How about recording a macro when you copy that sheet to a new workbook.

Then apply the data|filter there?

You could apply the "opposite" criteria in your filter and then delete the
visible rows and then remove the filter.

Thanks for your help.

The problem with the filter is that it's a shared workbook and for the
advanced filter, you have to unshare the workbook.

Does anyone know if this would be possible to do with a macro? A basic
looping if then statement.

I don't know how to compose macros but the general statement would be select
tab "Active B&P", if cell "C2" is greater than or equal to Tab "Labor $s"
cell "B1" then paste corresponding cell from "Active B&P" to the next empty
cell in column a on "Labor $'s"

Thanks again for the help!