[Solved] Populate multiple sheets from master sheet

[Xeipher]

I have tried to find the answer for the past two days and although I have found solutions that were close nothing has done the trick yet. What I am trying to accomplish is to take a list of items sold by a few different sellers and populate individual sheets based on seller. So something like taking one "Master" sheet:

Seller Item Price
----------------------
1 dog 2
2 cat 2
1 fish 1
3 frog 1

And Populate sheet "Seller 1":

Seller Item Price
----------------------
1 dog 2
1 fish 1

Populate sheet "Seller 2":

Seller Item Price
----------------------
2 cat 2

Populate sheet "Seller 3":

Seller Item Price
----------------------
3 frog 1

Any help on the subject would be very helpful. I would like to not have to copy and paste everything by hand as this is something I have to do every week.

Thank you

[Villeroy]

Data>Filter>AutoFilter.
Pick one item.

[Xeipher]

That will just change how the master list is displayed. I am looking to have separate sheets auto-populated with all the rows corresponding to the individual sellers.

[Villeroy]

Why? What is the difference? Instead of clicking a separate sheet from the sheet tabs you pick another entry from the auto-filter.

[Xeipher]

Because my boss wants a sheet containing all items and separate sheets for each seller. So I either automate it or I have to do it by hand constantly.

[Villeroy]

These are the requirements that give birth to great Basic coders.

[Xeipher]

Well so far I have been able to make it easier by defining names for the first cell in each sheet, then doing multiple standard filters on each seller with "Copy results to" directed at the names I defined. It gave me the desired result, but is there any way to automate it?

[Villeroy]

Nearly 100% (90 at least) of all Calc questions are database questions because today's spreadsheet users misuse spreadheets as database surrogates. Serving as poor mans database is not the main profession of spreadsheet software. In fact these programs are not very good at it. In many cases the solution lies in the Base component even if your stupid boss insists in using spreadsheets.
Are the category names (Sellers) more or less constant over time? How many are they? Is aggregation the final goal? (aggregation=sum, average, deviation of sales for each seller per time interval)

[Xeipher]

Yes they are more or less constant. There are 5 sellers and aggregation is one goal, total sales have to be itemized and matched from receipts that can contain items from multiple sellers. Then totals and lists for individual sellers are created from the master list of all sales and prices and dates etc.

[Villeroy]

The most "database-ish" spreadsheet feature is the data pilot (aka pivot table).
download/file.php?id=18838 is an example file of mine.
The first sheet is a flat database-like list like yours.
The second sheet has 2 lists for the validation feature on the first sheet (not important yet).
The 3rd sheet has a pivot table. Play with it. You can rearrange this report by dragging around the grey cells and you can double-click one of the numeric aggragations (sums of values for each person and category within a time interval). When you double-click any of the numbers, a new sheet will inserted. It contains a data copy of the original data that make up the double-clicked number.

[Villeroy]

Second method is more convoluted, but more like what you asked for.
Select your list including the header row.
menu:Data>Define... give a name to the list (say "Data"), [Add], [OK]
File>New>Database...
[X] Connect to existing database of type "Spreadsheet"
Specify the spreadsheet file.
[X] Register the database
Save the database.
In the tables section you see your "Data" table as if it were a table of a database. In fact all your data are still in the spreadsheet, they remain in the spreadsheet and they are editable in Calc.
Select the queries container and choose "Create a new query in SQL view".
Enter the following:
SELECT * FROM "Data" WHERE "Seller" = 'Joe'
Where the double-quoted "Data" is the table name, "Seller" is the column header above the seller names and single-quoted 'Joe' is the name of one seller. The * stands for "all columns".
Save the query as, say Joe and keep it open.
Edit the query, change the name to 'Jim' and save it as Jim.
Save a new copy of the query for each member of your small group of sales men.
Save the database and close the database.
Insert one sheet per sales man.
Hit F4.
Double-click the name of the database and then the queries.
Select John's spreadsheet and drag his query onto the sheet.
Do the same with the other sheets and queries.

Call menu:Data>Define...
You see one import range per sales man.
Click [More Options]
Click the name of an import range, check all the extra options below [More Options], then [Modify].
Finish the dialog with [OK] and save the spreadsheet document.

When you re-load the spreadsheet you will be prompted to refresh the import ranges. Click [Yes] and all the import ranges will be refreshed from the database (which is just a representation of your first sheet).

[MrProgrammer]

Hi, and welcome to the forum.

What I am trying to accomplish is to take a list of items sold by a few different sellers and populate individual sheets based on seller

Topic 47586: I want to split this list into different sheets, one sheet per class(class1, class2 etc)
[Tutorial] Sorting and Filtering data with formulas

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Xeipher]

Working great now. Thank you

[Xeipher]

Follow up question. I used the second method that Villeroy suggested. Worked great. However now when I open the document and click to yes to repeat queries it stops responding. I thought maybe it was just my computer lagging so I let it go all night with no response from the program. To try and make it a little more streamlined I restricted the "Data" range to only include the Columns needed, instead of having it go all the way to AMJ. Any suggestions to try and solve this issue would be helpful. Thank you.

[Villeroy]

If your query returns 10 columns and 99 rows, the imported data range should be A1:J100 (including the header row). I've never seen an import range expanding to all the columns up to AMJ.
Did you define the entire sheet as source range?
When you open the source sheet and hit Ctrl+End, which cell do you select?

[Alex1]

The easiest method is showing your boss how to use filters. Then this whole operation isn't necessary at all.

[Xeipher]

Agreed. Haha Sadly they refuse. They think the CD Rom in their desktop is a cup holder. I can work around the issue. Just trying to make things easier for the future.