Separate raw data onto different worksheets

[JN]

I have a worksheet that has a list of parts and the stockrooms they are in.
I want to have one sheet will all the raw data like so:

STOCKROOM PARTNUMBER QTY
----------------------------------------------
MAINSTOCK 123456789ABC 100
AUX-STOCK 123456789ABC 100
FINISHEDSTK XYZXYZ12345 500
MAINSTOCK XYZXYZ12345 100

And then create separate worksheets with the stockroom name in cell A1.
Based on A1 (Stockroom name) I want the remainder of the sheet to be filled
with all the rows from the Raw Data sheet so when I add or refresh data each
of the other sheets will refresh with the correct information.

 

[Bernie Deitrick]

JN,

Don't use separate sheets - generally, a bad idea - just apply data filters
and choose the desired stockroom from the drop down. And you can use pivot
tables to create summaries.

HTH,
Bernie
MS Excel MVP

 

[JN]

Thanks, I could do that, but I need this setup for a very specific reason.
Is there a function for this?

 

[Dave Peterson]

Nope.

But if you lower your requirements, you may be able to use a macro that would
separate the data into different worksheets. Changes made to any of the
worksheets won't be reflected in any other, though.

Personally, I'd do my best to follow Bernie's suggestion. Having multiple
copies of the same data is just trouble waiting to happen.

But a compromise is to update just that single worksheet (with all the data) and
create new worksheets everytime you need them. Those new worksheets would not
be for updates, though--essentially just reports.

I'd start looking at one of these references:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or:

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

 

[MyVeryOwnSelf]

I have a worksheet that has a list of parts and the stockrooms they

are in. I want to have one sheet will all the raw data like so:

STOCKROOM PARTNUMBER QTY
----------------------------------------------
MAINSTOCK 123456789ABC 100
AUX-STOCK 123456789ABC 100
FINISHEDSTK XYZXYZ12345 500
MAINSTOCK XYZXYZ12345 100

And then create separate worksheets with the stockroom name in cell
A1. Based on A1 (Stockroom name) I want the remainder of the sheet to
be filled with all the rows from the Raw Data sheet so when I add or
refresh data each of the other sheets will refresh with the correct
information.

Here's one way.

Put the data above in Sheet1!B. Column A will be a helper column; it can
be hidden later for neatness.

In Sheet1!A1 put
=COUNTIF($B$1:$B1,B1)&"."&B1
and copy down as far as needed.

In Sheet2!A1 put a stockroom name.

In Sheet2!B1 put
=VLOOKUP((ROW()-1)&"."&$A$1,Sheet1!$A:$J,COLUMN()+1,FALSE)
and copy to the right and down as far as needed.

You will get #N/A for columns at the bottom, and zero where a cell in
Sheet1 is empty. To avoid this, expand the above VLOOKUP formula to
something like:
IF(OR(ISNA(above_formula),(above_formula)=""),"", above_formula)

Repeat for Sheet3 and a different stockroom name, etc.