View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Toppers Toppers is offline
external usenet poster
 
Posts: 4,339
Default scanning worksheet for data then adding it

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!