Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
"Copying" info between workbooks
Hi,
A B C D PRODUCT CODE SUPPLIER DESCRIPTION STOCK QUANTITY BD1245LK PK02 NOTEBOOKS 5 JL5589X SD01 COPY PAPER 8 Above is an example of my very basic STOCKSHEET, excel 2003. I have about 200 rows of information, all different suppliers. What I want to do is use column b to make up a new workbook according to supplier only, with the corresponding info in the row all being copyied across. Also, as I update, change and add new stock items to STOCKSHEET, I want the workbook containing the supplier info to update as well. Can anyone help? Thank you. -- Kind regards, Sarah |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
"Copying" info between workbooks
Hi Sarah
You will save yourself a lot of heartache further down the line if you keep all your data on one sheet, rather than trying to split it out. Apply an Autofilter to row 1, then just filter on column B to select the Supplier you want to see. If necessary, insert a few rows of data above your existing header, and use the Subtotal Function to Sum any values you want e.g if your insert 3 rows above your header, so your true data starts in row 5 In cell D1 enter =SUBTOTAL(9,$D2:$D1000) This will total the visible rows of data. Alternatively, (and a far better solution), create a Pivot table Place your cursor in cell A1DataPivot TableFinish On the PT skeleton that appears on a new sheet Drag Supplier to the Page area Drag Product Code to the Row area Drag Description to the Row area, below Product Code Drag Quantity to the Data area as Sum of Quantity Double click on Product CodeSubtotalsNone Select Supplier required from dropdown on page area. If you like the Pivot table method, then there are a few further refinements you will need to make to enable it to continue to reflect data as you add more information to the source info on Sheet1. InsertNameDefine Name myData Refers to =$A41:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1)) This will give a Dynamic range which will grow as you add more lines of data to your source table. Right click on any cell on the PTPT WizardbackChange source =myData After you add or amend any information in your source data on Sheet1, when you go to the Pivot Table, right click any cell and choose Refresh Data to reflect the changes. For more help take a look at http://www.datapigtechnologies.com/ExcelMain.htm and http://www.edferrero.com/Excel Tutorials/PivotTableTutorial2003/tabbed/89/Default.aspx and http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/tiptech.html scroll to section on pivot tables -- Regards Roger Govier "Sarah" wrote in message ... Hi, A B C D PRODUCT CODE SUPPLIER DESCRIPTION STOCK QUANTITY BD1245LK PK02 NOTEBOOKS 5 JL5589X SD01 COPY PAPER 8 Above is an example of my very basic STOCKSHEET, excel 2003. I have about 200 rows of information, all different suppliers. What I want to do is use column b to make up a new workbook according to supplier only, with the corresponding info in the row all being copyied across. Also, as I update, change and add new stock items to STOCKSHEET, I want the workbook containing the supplier info to update as well. Can anyone help? Thank you. -- Kind regards, Sarah |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
"Copying" info between workbooks
Hi Roger,
Thank you so much for you detailed solutions. I went with option 2: The Pivot Table, and am very happy with the results - it's exactly what I needed! Thank you Roger. There is just one thing Roger, some of the cells in the "STOCK QUANTITY" column are blank, and "(blank)" is being returned in these cells, is there an easy way to leave the cell empty? -- Kind regards, Sarah "Roger Govier" wrote: Hi Sarah You will save yourself a lot of heartache further down the line if you keep all your data on one sheet, rather than trying to split it out. Apply an Autofilter to row 1, then just filter on column B to select the Supplier you want to see. If necessary, insert a few rows of data above your existing header, and use the Subtotal Function to Sum any values you want e.g if your insert 3 rows above your header, so your true data starts in row 5 In cell D1 enter =SUBTOTAL(9,$D2:$D1000) This will total the visible rows of data. Alternatively, (and a far better solution), create a Pivot table Place your cursor in cell A1DataPivot TableFinish On the PT skeleton that appears on a new sheet Drag Supplier to the Page area Drag Product Code to the Row area Drag Description to the Row area, below Product Code Drag Quantity to the Data area as Sum of Quantity Double click on Product CodeSubtotalsNone Select Supplier required from dropdown on page area. If you like the Pivot table method, then there are a few further refinements you will need to make to enable it to continue to reflect data as you add more information to the source info on Sheet1. InsertNameDefine Name myData Refers to =$A41:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1)) This will give a Dynamic range which will grow as you add more lines of data to your source table. Right click on any cell on the PTPT WizardbackChange source =myData After you add or amend any information in your source data on Sheet1, when you go to the Pivot Table, right click any cell and choose Refresh Data to reflect the changes. For more help take a look at http://www.datapigtechnologies.com/ExcelMain.htm and http://www.edferrero.com/Excel Tutorials/PivotTableTutorial2003/tabbed/89/Default.aspx and http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/tiptech.html scroll to section on pivot tables -- Regards Roger Govier "Sarah" wrote in message ... Hi, A B C D PRODUCT CODE SUPPLIER DESCRIPTION STOCK QUANTITY BD1245LK PK02 NOTEBOOKS 5 JL5589X SD01 COPY PAPER 8 Above is an example of my very basic STOCKSHEET, excel 2003. I have about 200 rows of information, all different suppliers. What I want to do is use column b to make up a new workbook according to supplier only, with the corresponding info in the row all being copyied across. Also, as I update, change and add new stock items to STOCKSHEET, I want the workbook containing the supplier info to update as well. Can anyone help? Thank you. -- Kind regards, Sarah |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
"Copying" info between workbooks
Hi Sarah
So glad you went with PT's. On the dropdown for Stock Quantity, just de-select Blank -- Regards Roger Govier "Sarah" wrote in message ... Hi Roger, Thank you so much for you detailed solutions. I went with option 2: The Pivot Table, and am very happy with the results - it's exactly what I needed! Thank you Roger. There is just one thing Roger, some of the cells in the "STOCK QUANTITY" column are blank, and "(blank)" is being returned in these cells, is there an easy way to leave the cell empty? -- Kind regards, Sarah "Roger Govier" wrote: Hi Sarah You will save yourself a lot of heartache further down the line if you keep all your data on one sheet, rather than trying to split it out. Apply an Autofilter to row 1, then just filter on column B to select the Supplier you want to see. If necessary, insert a few rows of data above your existing header, and use the Subtotal Function to Sum any values you want e.g if your insert 3 rows above your header, so your true data starts in row 5 In cell D1 enter =SUBTOTAL(9,$D2:$D1000) This will total the visible rows of data. Alternatively, (and a far better solution), create a Pivot table Place your cursor in cell A1DataPivot TableFinish On the PT skeleton that appears on a new sheet Drag Supplier to the Page area Drag Product Code to the Row area Drag Description to the Row area, below Product Code Drag Quantity to the Data area as Sum of Quantity Double click on Product CodeSubtotalsNone Select Supplier required from dropdown on page area. If you like the Pivot table method, then there are a few further refinements you will need to make to enable it to continue to reflect data as you add more information to the source info on Sheet1. InsertNameDefine Name myData Refers to =$A41:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1)) This will give a Dynamic range which will grow as you add more lines of data to your source table. Right click on any cell on the PTPT WizardbackChange source =myData After you add or amend any information in your source data on Sheet1, when you go to the Pivot Table, right click any cell and choose Refresh Data to reflect the changes. For more help take a look at http://www.datapigtechnologies.com/ExcelMain.htm and http://www.edferrero.com/Excel Tutorials/PivotTableTutorial2003/tabbed/89/Default.aspx and http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/tiptech.html scroll to section on pivot tables -- Regards Roger Govier "Sarah" wrote in message ... Hi, A B C D PRODUCT CODE SUPPLIER DESCRIPTION STOCK QUANTITY BD1245LK PK02 NOTEBOOKS 5 JL5589X SD01 COPY PAPER 8 Above is an example of my very basic STOCKSHEET, excel 2003. I have about 200 rows of information, all different suppliers. What I want to do is use column b to make up a new workbook according to supplier only, with the corresponding info in the row all being copyied across. Also, as I update, change and add new stock items to STOCKSHEET, I want the workbook containing the supplier info to update as well. Can anyone help? Thank you. -- Kind regards, Sarah |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
"Copying" info between workbooks
Hi again Roger,
Thank you once again. Such a simple solution, hey... You've been a great help Roger, many thanks. -- Kind regards, Sarah "Roger Govier" wrote: Hi Sarah So glad you went with PT's. On the dropdown for Stock Quantity, just de-select Blank -- Regards Roger Govier "Sarah" wrote in message ... Hi Roger, Thank you so much for you detailed solutions. I went with option 2: The Pivot Table, and am very happy with the results - it's exactly what I needed! Thank you Roger. There is just one thing Roger, some of the cells in the "STOCK QUANTITY" column are blank, and "(blank)" is being returned in these cells, is there an easy way to leave the cell empty? -- Kind regards, Sarah "Roger Govier" wrote: Hi Sarah You will save yourself a lot of heartache further down the line if you keep all your data on one sheet, rather than trying to split it out. Apply an Autofilter to row 1, then just filter on column B to select the Supplier you want to see. If necessary, insert a few rows of data above your existing header, and use the Subtotal Function to Sum any values you want e.g if your insert 3 rows above your header, so your true data starts in row 5 In cell D1 enter =SUBTOTAL(9,$D2:$D1000) This will total the visible rows of data. Alternatively, (and a far better solution), create a Pivot table Place your cursor in cell A1DataPivot TableFinish On the PT skeleton that appears on a new sheet Drag Supplier to the Page area Drag Product Code to the Row area Drag Description to the Row area, below Product Code Drag Quantity to the Data area as Sum of Quantity Double click on Product CodeSubtotalsNone Select Supplier required from dropdown on page area. If you like the Pivot table method, then there are a few further refinements you will need to make to enable it to continue to reflect data as you add more information to the source info on Sheet1. InsertNameDefine Name myData Refers to =$A41:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1)) This will give a Dynamic range which will grow as you add more lines of data to your source table. Right click on any cell on the PTPT WizardbackChange source =myData After you add or amend any information in your source data on Sheet1, when you go to the Pivot Table, right click any cell and choose Refresh Data to reflect the changes. For more help take a look at http://www.datapigtechnologies.com/ExcelMain.htm and http://www.edferrero.com/Excel Tutorials/PivotTableTutorial2003/tabbed/89/Default.aspx and http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/tiptech.html scroll to section on pivot tables -- Regards Roger Govier "Sarah" wrote in message ... Hi, A B C D PRODUCT CODE SUPPLIER DESCRIPTION STOCK QUANTITY BD1245LK PK02 NOTEBOOKS 5 JL5589X SD01 COPY PAPER 8 Above is an example of my very basic STOCKSHEET, excel 2003. I have about 200 rows of information, all different suppliers. What I want to do is use column b to make up a new workbook according to supplier only, with the corresponding info in the row all being copyied across. Also, as I update, change and add new stock items to STOCKSHEET, I want the workbook containing the supplier info to update as well. Can anyone help? Thank you. -- Kind regards, Sarah |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Multiple "source" workbooks linked to single "destination" workboo | Excel Worksheet Functions | |||
Retrieving info from "child" to a "parent" document | Excel Discussion (Misc queries) | |||
problem with Linking workbooks via "copy" and "paste link" | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |