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

Steve Swift

Board Regular
Joined
Jan 18, 2004
Messages
187
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
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

Board Regular
Joined
Jan 18, 2004
Messages
187
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

Legend
Joined
Sep 3, 2002
Messages
10,473
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

Board Regular
Joined
Jan 18, 2004
Messages
187

ADVERTISEMENT

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

Legend
Joined
Sep 3, 2002
Messages
10,473
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

Board Regular
Joined
Oct 13, 2003
Messages
144
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

Board Regular
Joined
Oct 13, 2003
Messages
144
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,332
Messages
5,769,470
Members
425,552
Latest member
learnerrr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top