Add a row based on other criteria

[Joe Gieder]

Is it possible to do this, have two worksheets one with Summary data and one
with Detail data have the Summary sheet constantly look at the Detail sheet
and if there has been new data entered on the Detail sheet automatically
enter it on the Summary sheet. I have sales orders that have many many items
listed for each number (the sales order is unique and can listed a hundred
times) them and I want the sales order numer to be entered one time on the
Summary sheet and then count how many items there are for that sales order
and put it on the Summary sheet.

TIA
Joe

 

[Joe Gieder]

I want to insert a row at the beggining not add it to the end if it's possible.

Joe

 

[Roger Govier]

Hi Joe

Depending upon the layout of the data in your Detail sheet, you may well
be able to create your Summary by using a Pivot Table report.

Take a look at Debra Dalgleish's site for help on setting up Pivot
Tables
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/tiptech.html scroll to the section on Pivot
Tables

or for a video demonstration then look at Mike Alexander's site
http://www.datapigtechnologies.com/flashfiles/pivot1.html

 

[Joe Gieder]

Thanks for the idea. I have one problem though I share my Detail workbook
with several others and I saw that a Pivot Table cannot be updated while
sharing and you cannot share a workbook with links to another. I don't know
if VB would be an answer but I know nothing about VB.

Thank you for your advise.
Joe

 

[Roger Govier]

Hi Joe

You are quite correct about shared workbooks and Pivot Tables.
You can link another workbook to the Shared workbook, by telling the PT
to Get Data from an external source (albeit it is another Excel
workbook). It will not refresh whilst the workbook is shared, but once
you remove sharing, it can be refreshed, then set the source back to
being shared again. Probably not the best way of doing what you want.

Before resorting to VBA, if it is only a count of the items belonging to
each Order numbers that you want, then you could do the following.

Select Columns A and B of Sheet Summary
Data>Consolidate>use dropdown to Select Count>tick Use labels in left
column>Reference>click Browse and select columns A and B of Detail>OK

I am assuming that column A of Detail contains Order Number and column B
will also have values contained on each row.
Adjust as appropriate.

Just tried this in a Shared workbook and it works fine.