Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
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 |