"Copying" info between workbooks

[Sarah]

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.

 

[Roger Govier]

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 A1>Data>Pivot Table>Finish
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 Code>Subtotals>None

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.
Insert>Name>Define>
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 PT>PT Wizard>back>Change 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

 

[Sarah]

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

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 A1>Data>Pivot Table>Finish
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 Code>Subtotals>None

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.
Insert>Name>Define>
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 PT>PT Wizard>back>Change 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

 

[Roger Govier]

Hi Sarah

So glad you went with PT's.
On the dropdown for Stock Quantity, just de-select Blank

--
Regards
Roger Govier

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?

 

[Sarah]

Hi again Roger,

Thank you once again. Such a simple solution, hey... You've been a great
help Roger, many thanks.