How do I create spreadsheets that auto populate using information from main spreadsheet

Hello,

I have a main spreadsheet that I am consistenty adding information to. The columns are : Company name, Contact, Territory, and Status. The main spreadsheet is titled "Main". I want 5 additional spreadsheets in the same workbook that are automatically pulling information from the "Main" spreadsheet, and populating the appropriate spreadsheet . For example...I have 500 entries of different companies in "Main".
All of these companies are either categorized as "North, South, East, West, Offshore" in the Territory column. So, I would like my workbook to have 6 tabs...one "Main, North, South, East, West, Offshore." As of right now, I am Sorting the column, then copy and pasting into correct spreadsheet manually. Just hoping to simplify. thank you for any help.

Sample.xls Please see attached document for sample of what I am looking for. I want to enter records into the "Main" Tabe...and have the appropriate tab populated. All records with North..put on North spreadsheet. All South...put into the South spreadsheet.

FDibbins,
I thank you so much..but please excuse my ignorance. "m copied down"?? I am not sure what you mean. Also, "copied down and across"...does this mean selecting the cells down and then pasting the formula you provided?? Sorry it is very late here and I am exhausted mentally and physically. I really appreciate your patience for this young and frustrated user

Sample2.xls FDibbins,

I seem to be getting an error for missing a parenthesis. I am to my wits end and exhausted. Could you insert the formulas to the attachement and resubmit? I will just take that and run with it. Thanks again. TAke your time...I will have to check this thread tomorrow. I must retire for the night...I can't go anylonger. I greatly appreciate your time.

2012 Target LisTEST SAmplet.xlsx
So i was able to get the formulas to work like a charm for my smaller version i created for demo purposes. However, when I applied the formula to my actual workbook...I had to change a few things. Please see attached spreadsheet.

So, I was not only changing the "North," and "South etc"...I had to changed the number of columns and the Index from "Main" to "Master List". Unfortunately, I continue to get errors. Please help. Thanks again FDibbins

Hi again...please don't give up on me. For some reason, everything is working except for the company name. a "0" is placed in that column. Here are my steps and formulas.


Open Master List spreadsheet and I copy this to J2 all the way down as far as I need to go:
=B2&COUNTIF($B$2:B2,B2)

Then, each spreadsheet I enter the appropriate formula into A2, then drag across to I, then down as far as I need to go.
=IFERROR(INDEX('MASTER LIST'!$A$1:$I$300,MATCH("Lafayette"&ROW(A2),'MASTER LIST'!$J$1:$J$300,0),MATCH(A$1,'MASTER LIST'!$A$1:$I$1,0)),"")
=IFERROR(INDEX('MASTER LIST'!$A$1:$I$300,MATCH("Houma"&ROW(A2),'MASTER LIST'!$J$1:$J$300,0),MATCH(A$1,'MASTER LIST'!$A$1:$I$1,0)),"")
=IFERROR(INDEX('MASTER LIST'!$A$1:$I$300,MATCH("Baton Rouge"&ROW(A2),'MASTER LIST'!$J$1:$J$300,0),MATCH(A$1,'MASTER LIST'!$A$1:$I$1,0)),"")
=IFERROR(INDEX('MASTER LIST'!$A$1:$I$300,MATCH("South La"&ROW(A2),'MASTER LIST'!$J$1:$J$300,0),MATCH(A$1,'MASTER LIST'!$A$1:$I$1,0)),"")
=IFERROR(INDEX('MASTER LIST'!$A$1:$I$300,MATCH("New Orleans"&ROW(A2),'MASTER LIST'!$J$1:$J$300,0),MATCH(A$1,'MASTER LIST'!$A$1:$I$1,0)),"")
=IFERROR(INDEX('MASTER LIST'!$A$1:$I$300,MATCH("Shreveport"&ROW(A2),'MASTER LIST'!$J$1:$J$300,0),MATCH(A$1,'MASTER LIST'!$A$1:$I$1,0)),"")


Where is my mix up???

Originally Posted by sgassen

Hi again...please don't give up on me. For some reason, everything is working except for the company name. a "0" is placed in that column.

Where is my mix up???

Hello,

Change "Company Name" in A1 in sheet Master List, with "Customer" and the formula will work.

Ok, thank you both for the help thus far...I am almost there. I was able to successfully populate all my spreadsheets according to Territory. I can also successfully edit any of the existing records in the Master List and the changes are instantly update on the appropriate spreadsheet. However, i still have an error somewhere. If I ADD a new record to the Master List, it does not add it to the correct spreadsheet. Is there something I am missing??? I can't figure it out because my smaller sample workbook works correctly. I hope the size of my actual workook does not have something to do with that. Any ideas???

by Add, I mean adding addtional records to my master list. I am adding additional companies(Rows).
I did drag the =B2&COUNTIF($B$2:B2,B2 very far to give myself room to add more records. in the colums, it shows a "0" so it is active for those cells. Its very weird...I have compared it to my sample and cant figure it out. I was hoping there was an "update or refresh" button.

Yes, I am typing into the next blank row. The helper does change from "0" to the sheet name.
Spelling is the same...as I type in the territory name, the helper recognizes the territory and completes the word.

Originally Posted by newdoverman

Instead of having a whole bunch of worksheets, why not make use of a Table on the main page or use a Pivot Table like I have in the enclosed file?

Yes, I am willing to try this. Could you please provide steps for creating my Pivot Table?

thank you. I have my Pivot table set up and it will work. Not completely what i wanted, but I will need to sit down and redo my formulas the way fdibbins was suggesting. thank you both for the help.