ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Pull info from separate worksheet based on given criteria (https://www.excelbanter.com/excel-discussion-misc-queries/203085-pull-info-separate-worksheet-based-given-criteria.html)

chas

Pull info from separate worksheet based on given criteria
 
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

Pull info from separate worksheet based on given criteria
 
vlookup formula using the !sheetname referencing will work

"Chas" wrote:

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




chas

Pull info from separate worksheet based on given criteria
 
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.

"dlw" wrote:

vlookup formula using the !sheetname referencing will work

"Chas" wrote:

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




chas

Pull info from separate worksheet based on given criteria
 
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

"Chas" wrote:

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




Dave Peterson

Pull info from separate worksheet based on given criteria
 
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 wrote:

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.

"dlw" wrote:

vlookup formula using the !sheetname referencing will work

"Chas" wrote:

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




--

Dave Peterson

chas

Pull info from separate worksheet based on given criteria
 
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" wrote:

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 wrote:

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.

"dlw" wrote:

vlookup formula using the !sheetname referencing will work

"Chas" wrote:

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




--

Dave Peterson


Dave Peterson

Pull info from separate worksheet based on given criteria
 
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 wrote:

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" wrote:

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 wrote:

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.

"dlw" wrote:

vlookup formula using the !sheetname referencing will work

"Chas" wrote:

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




--

Dave Peterson


--

Dave Peterson

chas

Pull info from separate worksheet based on given criteria
 
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" wrote:

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 wrote:

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" wrote:

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 wrote:

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.

"dlw" wrote:

vlookup formula using the !sheetname referencing will work

"Chas" wrote:

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




--

Dave Peterson


--

Dave Peterson


Dave Peterson

Pull info from separate worksheet based on given criteria
 
I would filter the data and copy the visible rows to the other location.

But someone else may chime in with a formula approach.

Chas wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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.

"dlw" wrote:

vlookup formula using the !sheetname referencing will work

"Chas" wrote:

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




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

chas

Pull info from separate worksheet based on given criteria
 
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" wrote:

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

But someone else may chime in with a formula approach.

Chas wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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.

"dlw" wrote:

vlookup formula using the !sheetname referencing will work

"Chas" wrote:

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




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


Dave Peterson

Pull info from separate worksheet based on given criteria
 
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.

Chas wrote:

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" wrote:

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

But someone else may chime in with a formula approach.

Chas wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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.

"dlw" wrote:

vlookup formula using the !sheetname referencing will work

"Chas" wrote:

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




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 02:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com