So...I have a workbook with multiple worksheets. In the first worksheet,
there are a number of rows and columns...it's a forecast worksheet.
One of the columns contains product names. I want to, from another
worksheet, scan the list (column) for one of the products and take that row
and add it to the worksheet I'm working on.
So, for example, if a workbook contains 3 worksheets and I'm working in #3
but #1 contains the data I want.
Worksheet #1 has a column with Microsoft products...it would be 1 or more
products. So, a column could say "Excel" or "Excel and Word" (without the
quotes).
I want to work in worksheet 3 and scan the "products" column to find each
one that has the word "Excel" in it then take that row and populate rows in
worksheet #3.
I hope this makes sense. Thanks!

Try:

Assumes column A contains the Product Code

=IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH("Excel",Sheet 1!$A$1:$A$20)),ROW(Sheet1!$A$1:$A$20),""),ROW($A1) )),"",INDEX(Sheet1!A$1:A$20,N(SMALL(IF(ISNUMBER(SE ARCH("Excel",Sheet1!$A$1:$A$20)),ROW(Sheet1!$A$1:$ A$20),""),ROW($A1)))))

Enter with Ctrl+Shift+Enter

The above will return the product code (INDEX(Sheet1!A$1:A$20)

Copy across (to return columns B onwards) and down until column A is blank

Replace "Excel" with A cell containing "search" parameter

HTH

"Steve" wrote:

So...I have a workbook with multiple worksheets. In the first worksheet,
there are a number of rows and columns...it's a forecast worksheet.
One of the columns contains product names. I want to, from another
worksheet, scan the list (column) for one of the products and take that row
and add it to the worksheet I'm working on.
So, for example, if a workbook contains 3 worksheets and I'm working in #3
but #1 contains the data I want.
Worksheet #1 has a column with Microsoft products...it would be 1 or more
products. So, a column could say "Excel" or "Excel and Word" (without the
quotes).
I want to work in worksheet 3 and scan the "products" column to find each
one that has the word "Excel" in it then take that row and populate rows in
worksheet #3.
I hope this makes sense. Thanks!

I'm not sure this is what you want, but perhaps:
Set up the list of products on sheet #1 as a Named Range so that it can be
used as a data validation list on another sheet. In sheet #3, select a group
of cells where you want to be able to select the product(s) from and pull
related data from sheet 1. Set those up with Data | Validation using the
list on sheet #1 as the list source - this guarantees you always 'type' them
properly without error. In the cells next to each of those, set up VLOOKUP()
formulas to pull the data from sheet #1 that you need.

This workbook shows this process in action, and tells how it was set up,
click the link and save to your hard drive and take a look at it. Link is
one continuous line, if it doesn't seem to work, copy it all and paste into
your browser's address bar:
http://www.jlathamsite.com/uploads/D...arateSheet.xls

"Steve" wrote:

So...I have a workbook with multiple worksheets. In the first worksheet,
there are a number of rows and columns...it's a forecast worksheet.
One of the columns contains product names. I want to, from another
worksheet, scan the list (column) for one of the products and take that row
and add it to the worksheet I'm working on.
So, for example, if a workbook contains 3 worksheets and I'm working in #3
but #1 contains the data I want.
Worksheet #1 has a column with Microsoft products...it would be 1 or more
products. So, a column could say "Excel" or "Excel and Word" (without the
quotes).
I want to work in worksheet 3 and scan the "products" column to find each
one that has the word "Excel" in it then take that row and populate rows in
worksheet #3.
I hope this makes sense. Thanks!

you guys are amazing. thank you.

So, let me help a little further...
Tab 1 is called "Revenue Forecast." Tab 3 is called "TAO product sales
funnel."

tab 1:
col a: Client name
Col b: Blah blah
Col c: blah bla
Col d: Deal type (this is where the software is listed) - software here
could be TAO (the one I'm scanning for), LR, QC, or any combination of these
or even others...
Col e: Close date
Col f: Bookings...

Tab 3:
could have exact same information as tab 1 but I need to "Extract" out the
ones that show "TAO" somewhere in the col d/deal type.

I tried #1 (Toppers) and updated the relevant information (worksheet name,
column, etc) but got zilch back from Excel. I'm obviously missing something
and will scan further.

Number #2 (JLatham) is helpful and may come in handy for this at some point
but is already helpful for something else I'm working on. Thanks guys!

"Steve" wrote:

So...I have a workbook with multiple worksheets. In the first worksheet,
there are a number of rows and columns...it's a forecast worksheet.
One of the columns contains product names. I want to, from another
worksheet, scan the list (column) for one of the products and take that row
and add it to the worksheet I'm working on.
So, for example, if a workbook contains 3 worksheets and I'm working in #3
but #1 contains the data I want.
Worksheet #1 has a column with Microsoft products...it would be 1 or more
products. So, a column could say "Excel" or "Excel and Word" (without the
quotes).
I want to work in worksheet 3 and scan the "products" column to find each
one that has the word "Excel" in it then take that row and populate rows in
worksheet #3.
I hope this makes sense. Thanks!

try:

=IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH("TAO",Sheet3! $D$1:$D$20)),ROW(Sheet3!$D$1:$D$20),""),ROW($A1))) ,"",INDEX(Sheet3!A$1:A$20,N(SMALL(IF(ISNUMBER(SEAR CH("TAO",Sheet3!$D$1:$D$20)),ROW(Sheet3!$D$1:$D$20 ),""),ROW($A1)))))

Remember: Enter with Ctrl+ShifT+Enter

HTH

"Steve" wrote:

you guys are amazing. thank you.

So, let me help a little further...
Tab 1 is called "Revenue Forecast." Tab 3 is called "TAO product sales
funnel."

tab 1:
col a: Client name
Col b: Blah blah
Col c: blah bla
Col d: Deal type (this is where the software is listed) - software here
could be TAO (the one I'm scanning for), LR, QC, or any combination of these
or even others...
Col e: Close date
Col f: Bookings...

Tab 3:
could have exact same information as tab 1 but I need to "Extract" out the
ones that show "TAO" somewhere in the col d/deal type.

I tried #1 (Toppers) and updated the relevant information (worksheet name,
column, etc) but got zilch back from Excel. I'm obviously missing something
and will scan further.

Number #2 (JLatham) is helpful and may come in handy for this at some point
but is already helpful for something else I'm working on. Thanks guys!

"Steve" wrote:

So...I have a workbook with multiple worksheets. In the first worksheet,
there are a number of rows and columns...it's a forecast worksheet.
One of the columns contains product names. I want to, from another
worksheet, scan the list (column) for one of the products and take that row
and add it to the worksheet I'm working on.
So, for example, if a workbook contains 3 worksheets and I'm working in #3
but #1 contains the data I want.
Worksheet #1 has a column with Microsoft products...it would be 1 or more
products. So, a column could say "Excel" or "Excel and Word" (without the
quotes).
I want to work in worksheet 3 and scan the "products" column to find each
one that has the word "Excel" in it then take that row and populate rows in
worksheet #3.
I hope this makes sense. Thanks!