Fetch row of info from 1 sheet to another based on criteria

[Steve Swift]

I have 3 worksheets. Sheet 1 contains the name of various sales people in column A, e.g. 'John Smith' and the Month of the sale in Column B, e.g. 'March'

What I want to do is create a seperate worksheet for each sales person and then fetch all of the information to that worksheet if the name appears on sheet 1.

In summary, Sheet 1 contains all the data Row by Row.

Row 1,5,13,22,44, Column A, contains the sales persons name 'John Smith'
Row, 2,3,4,6, Column A, contains the sales persons name 'Fred Smith'
All other rows contain the sales person with the name of 'Bill Smith'
In all cases, Rows Columns B through G contain other info I need.

All the other sheets are named with the sales persons name 'John Smith'

On the John Smith sheet in cell A1 I want to look up 'Sheet 1' and say, IF anywhere in Column A you find the name 'John Smith' then fetch all of the information from that row and put it in the next available row.

Hope someone can help.

Thanks

Steve

 

[want2b]

Re: Fetch row of info from 1 sheet to another based on crite

A quick and dirty way to do this is:
Insert column (New "A") data fill top to bottom 1,2,3 etc
THen sort data on the (new "B") copy all the john smiths and the data needed to the second sheet. THen sort on column A and that puts the sheet back as it was then delete column A and all is as was with the infor for John Smith on the other page.

Simple but it will work.

 

[Steve Swift]

Re: Fetch row of info from 1 sheet to another based on crite

Thanks, but the trouble is I need this info to ongoing update, so that when I add info to the main sheet it automatically update the sales persons sheets.

 

[just_jon]

Re: Fetch row of info from 1 sheet to another based on crite

Do you really need the extra sheets? Would doing a filter on the main sheet, leaving visible only those matching records, do what you want?

 

[Steve Swift]

Re: Fetch row of info from 1 sheet to another based on crite

I could use a filter but then every time I need to see the figures I have to run the filter. In my example there are only 3 sales people. In real life there are 15.

 

[just_jon]

Re: Fetch row of info from 1 sheet to another based on crite

Well I think running the filter along with perhaps a few well placed formulas will be quicker than any pure formula set, and more flexible than a VBA solution. What's a typical record look like, an besides sorting by person what else needs to be done?

 

[howzat]

Re: Fetch row of info from 1 sheet to another based on crite

Hi Steve,

Here's a formula approach using the recommended add-in morefunc.XLL - found at http://longre.free.fr/english/index.html

In your 'John Smith' sheet highlight the blank range A2 to J100 (or whatever the applicable range of data is).

Press F2 and enter the following formula:

=VSORT(IF(Sheet1!A2:A100="John Smith",Sheet1!A2:J100,""))

Array entered by pressing Ctrl + Alt + Enter.

This will summarise all John Smith enties on Sheet 1.

Do this for each of your named sheets.

 

[howzat]

Re: Fetch row of info from 1 sheet to another based on crite

As an edit to the above post, from your first post I 'spose your range would be A:G, not A:J.

Further, with the Morefunc.xll add in, you can use the SHEETNAME() function instead of typing the name in each time i.e. "John Smith", provided the name of the sheet matched the names in the data on sheet1.

This will enable you to highlight all your sheets and enter the formula in the same way as indicated in my previous post. So now you formula should look something like this:

=VSORT(IF(Sheet1!A2:A100=SHEETNAME(),Sheet1!A2:G100,""))

Array entered by pressing Ctrl + Alt + Enter.

One last thing, not sure if this will be helpful or not. From another one of the recommended add ins ---> ASAP Utilities found at http://www.asap-utilities.com/, amongst many other useful functions, there's a quick and easy way of adding sheets with the sheet names defined by a range of selected cells, in your case it would be the names of the sales personnel.

Post back if you have any queries