Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-create separate worksheets...
I have a list in a single worksheet that runs as follows: Product no. Qty. 123 17 123 8 123 12 156 11 184 0 184 13 and so on. I have used a function previously that allows a new worksheet to be created within the workbook for each product no. Rather than cutting and pasting each block of product numbers into its own worksheet, there is a way to automatically create a new worksheet that, for example, contains all the 123 product data in worksheet 1, all the 156 product data in worksheet 2 etc. Any ideas how to do it as I can't remember and can't find anything related to it in the Excel help file? Thanks in advance. -- craig72 ------------------------------------------------------------------------ craig72's Profile: http://www.excelforum.com/member.php...o&userid=35540 View this thread: http://www.excelforum.com/showthread...hreadid=553034 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-create separate worksheets...
See
http://www.rondebruin.nl/copy5.htm Try http://www.rondebruin.nl/copy5.htm#all -- Regards Ron De Bruin http://www.rondebruin.nl "craig72" wrote in message ... I have a list in a single worksheet that runs as follows: Product no. Qty. 123 17 123 8 123 12 156 11 184 0 184 13 and so on. I have used a function previously that allows a new worksheet to be created within the workbook for each product no. Rather than cutting and pasting each block of product numbers into its own worksheet, there is a way to automatically create a new worksheet that, for example, contains all the 123 product data in worksheet 1, all the 156 product data in worksheet 2 etc. Any ideas how to do it as I can't remember and can't find anything related to it in the Excel help file? Thanks in advance. -- craig72 ------------------------------------------------------------------------ craig72's Profile: http://www.excelforum.com/member.php...o&userid=35540 View this thread: http://www.excelforum.com/showthread...hreadid=553034 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-create separate worksheets...
Thanks for your reply. I remember that there is a menu option somewhere in Excel that performs that function for you instead of having to use VBA or macros etc. Does anyone know if that is the case or not? I'm not too sure how to use macros and VBA. Thanks. -- craig72 ------------------------------------------------------------------------ craig72's Profile: http://www.excelforum.com/member.php...o&userid=35540 View this thread: http://www.excelforum.com/showthread...hreadid=553034 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-create separate worksheets...
Here's one play to automate it using non-array formulas ..
A sample construct is available at: http://www.savefile.com/files/5143636 AutoFiltering_Data_To_Resp_Sheet_NonArrayFormulas. xls In sheet: WS1 (the "master sheet") ------------------------------------------ Assume data in cols A to B, data in row2 down, with the key col = col A (Product #) Put in I2, copy down to say I20, to cover the max expected extent of data in col A: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW())) (Leave I1 empty) Put in J1, copy across to say Q1, to cover the max expected no. of unique products: =IF(COLUMN(A1)COUNT($I:$I),"",TEXT(INDEX($A:$A,MA TCH(SMALL($I:$I,COLUMN(A1)),$I:$I,0)),"000")) Put in K2: =IF(J$1="","",IF(TEXT($A2,"000")=J$1,ROW(),"")) Copy K2 across to Q2, fill down to Q20 to cover the max expected extent of data in col A (as per col I) Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1") ))+1,32) Click OK The above will define WSN as a name we can use refer to the sheetname in formulas. It will auto-extract the sheetname implicitly. Technique came from a post by Harlan. In a new sheet named as: 123 With the same col headers pasted into A1:B1 Put in A2: =IF(ROW(A1)COUNTIF(WS1!$A:$A,WSN),"", INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$I:$I,,MATCH( WSN,WS1!$J$1:$IV$1,0)),ROW(A1)), OFFSET(WS1!$I:$I,,MATCH(WSN,WS1!$J$1:$IV$1,0)),0)) ) Copy A2 across to B2, fill down to say B10, to cover the max expected # of lines for any one product (Fill down by the smallest extent sufficient to cover the max expected # of lines for any one product. Here, I've assumed that 9 rows (rows 2 to 10) is sufficient.) Cols A to B will return only the lines for product: 123 from "WS1", with all lines neatly bunched at the top. Now, just make a copy of the sheet: 123, rename it as the next product: 156, and we'd get the results for that product. Do note that if the product# is less than 3 digits, ie < 100, we need to name it (the sheet tab) with leading zeros. Eg: 099 for product 99, 008 for product 8, etc. Repeat the copy rename sheet process to get the rest of the product sheets (a one-time job). Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "craig72" wrote: I have a list in a single worksheet that runs as follows: Product no. Qty. 123 17 123 8 123 12 156 11 184 0 184 13 and so on. I have used a function previously that allows a new worksheet to be created within the workbook for each product no. Rather than cutting and pasting each block of product numbers into its own worksheet, there is a way to automatically create a new worksheet that, for example, contains all the 123 product data in worksheet 1, all the 156 product data in worksheet 2 etc. Any ideas how to do it as I can't remember and can't find anything related to it in the Excel help file? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create multiple worksheets from Page Pivots | Excel Worksheet Functions | |||
I want to create and name 365 worksheets... | Excel Worksheet Functions | |||
create yearly summary from monthly worksheets | Excel Discussion (Misc queries) | |||
Adding rows of different info from separate worksheets into summar | New Users to Excel | |||
Compare 2 Worksheets Create a 3rd depending on results | Excel Discussion (Misc queries) |