Splitting data from one spreadsheet into multiple spreadsheets

[yowzers]

I have on master spreadsheet on Sheet1. Column A has categories (i.e.
vegetables, fruits, nuts, etc.). Columns B through F have data corresponding
to that date (sales, expenses, profits, etc.).

I am trying to get the data from Sheet1 to split into multiple sheets based
on the category that was listed in Column A. For example, any time time
column A said Vegetables, it would copy that row's data into Sheet2.
Anytime column A said Fruits, it would copy that row's data into Sheet 3.

I know I can simply do this by using an IF formula in sheet2 stating that if
Sheet1!ColumnA states "Vegetables", copy the data into the corresponding
cells. However, that would keep the format on sheet 2 the same as sheet1.
So all the rows that don't state Vegetables in sheet 1 columnA would be blank
in sheet 2. I do not want all these blank rows in sheet 2, sheet 3, etc.

Is there any way to write a formula in sheet 2 that will fill each row
consecutively every time it finds that category in sheet 1 column A? I hope
I made myself clear. For example:

Sheet 1
Row Category
1 Vegetable
2 Fruit
3 Vegetable
4 Nuts
5 Vegetable

I want sheet 2 to look like this
1 Vegetable
2 Vegetable
3 Vegetable

 

[Bernard Liengme]

You and you alone know what is best for you work. But having said that it
should be pointed out that one of the cardinal rules for databases is not to
duplicate the data.

If you selected all the data in the master sheet and applied Data | Filter,
then any time you wishes you could use the filter to select, for example,
just "Nuts". You could even copy and paste it to its own worksheet to make a
special report and then delete that worksheet - data stored only ONCE

best wishes

 

[Ashish Mathur]

Hi,

you may refer to question 7 on the following link -
http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com