View Single Post
  #5   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:

=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!