Home 
Search 
Today's Posts 
#1




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




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




Pull info from separate worksheet based on given criteria
Unfortunately the projects won't be nicely organized the way my example was.
I believe Vlookup 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




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




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 Vlookup 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




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 Vlookup 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




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 Vlookup 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




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 Vlookup 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




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 Vlookup 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




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 Vlookup 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 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Summing totals on separate worksheet based on 2 criteria  Excel Discussion (Misc queries)  
how do i pull info from one worksheet to another?  Excel Discussion (Misc queries)  
Pull data from another sheet based on certain criteria  Excel Discussion (Misc queries)  
How do I pull a date from a separate worksheet using the IF functi  Excel Worksheet Functions  
Can a worksheet pull from a specific cell value in a separate wrks  Excel Worksheet Functions 