A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

scanning worksheet for data then adding it



 
 
Thread Tools Display Modes
  #1  
Old August 19th 07, 04:28 PM posted to microsoft.public.excel.worksheet.functions
Steve
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!
Ads
  #2  
Old August 19th 07, 04:52 PM posted to microsoft.public.excel.worksheet.functions
Toppers
external usenet poster
 
Posts: 4,340
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  
Old August 19th 07, 04:54 PM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 3,366
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  
Old August 19th 07, 05:40 PM posted to microsoft.public.excel.worksheet.functions
Steve
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  
Old August 19th 07, 06:36 PM posted to microsoft.public.excel.worksheet.functions
Toppers
external usenet poster
 
Posts: 4,340
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!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 03: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 07:22 PM


All times are GMT +1. The time now is 06:26 PM.


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