Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 40
Default 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



  #2   Report Post  
Posted to microsoft.public.excel.misc
dlw dlw is offline
external usenet poster
 
Posts: 510
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 40
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 40
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 40
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 40
Default 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

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 40
Default 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



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summing totals on separate worksheet based on 2 criteria Cheese_whiz Excel Discussion (Misc queries) 4 January 6th 08 11:34 PM
how do i pull info from one worksheet to another? cwjax Excel Discussion (Misc queries) 3 August 1st 07 11:22 PM
Pull data from another sheet based on certain criteria steve_sr2 Excel Discussion (Misc queries) 1 February 23rd 06 11:08 AM
How do I pull a date from a separate worksheet using the IF functi Jerrod Mason Excel Worksheet Functions 8 July 30th 05 06:35 AM
Can a worksheet pull from a specific cell value in a separate wrks reloanpro Excel Worksheet Functions 4 June 27th 05 10:03 PM


All times are GMT +1. The time now is 01:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"