Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,814
Default scanning worksheet for data then adding it

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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default scanning worksheet for data then adding it

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!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,814
Default scanning worksheet for data then adding it

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!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
brinnging data to excel via scanning a sheet. saqi New Users to Excel 3 March 28th 07 12:03 AM
INDEX and Scanning a Data Set in Excel Bryan Excel Worksheet Functions 3 February 5th 07 04:34 PM
Adding/Analyzing Data From One Worksheet to Another Leslie M Excel Worksheet Functions 1 September 11th 06 08:22 PM
adding new data to an excel worksheet HSUK Excel Discussion (Misc queries) 3 August 24th 06 12:32 PM
Scanning down a column lashio Excel Discussion (Misc queries) 0 February 12th 06 08:22 PM


All times are GMT +1. The time now is 01:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"