Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate raw data onto different worksheets
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate raw data onto different worksheets
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate raw data onto different worksheets
Thanks, I could do that, but I need this setup for a very specific reason.
Is there a function for this? "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate raw data onto different worksheets
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 JN wrote: Thanks, I could do that, but I need this setup for a very specific reason. Is there a function for this? "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate raw data onto different worksheets
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:D. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
... I want to chart data from 30 separate worksheets ... | Excel Discussion (Misc queries) | |||
Compare Data in two Separate Worksheets | Excel Discussion (Misc queries) | |||
Save 2 separate data imports in separate worksheets on the same ex | Excel Worksheet Functions | |||
Compare data on 2 separate worksheets. | Excel Worksheet Functions | |||
need help comparing data in 2 separate worksheets | Excel Worksheet Functions |