Add incremental information from a sheet to another

milt2004

New Member
Joined
Sep 4, 2004
Messages
28
I have a DATABASE SHEET which should take information from different sheets (“order sheet”) that I create from a template.
I want every time that I create a new “order sheet” the database sheet (the first column only, named “Quantity”) to be informed automatically.

How can I do this?

To understand better my problem I’ve made an example with two different “order sheets” and the respective database.


first order:
Book1
ABCDE
1(first)OrderSheet
2QuantityTypeNameMetals
31ringwalkgoldsilver
42braceletstarsilver
51ringmerlingold
61ringcamaleongold
72braceletsnakesilver
81ringbirdgold
Sheet1


first database:
Book1
ABCDE
1(first)DatabaseSheet
2QuantityTypeNameMetals
31ringwalkgoldsilver
42braceletstarsilver
51ringmerlingold
61ringcamaleongold
72braceletsnakesilver
81ringbirdgold
Sheet2


second order:
Book1
ABCDE
1(second)OrderSheet
2QuantityTypeNameMetals
31ringwalkgoldsilver
41braceletstarsilver
53ringmerlingold
62ringcamaleongold
71braceletsnakesilver
82ringbirdgold
Sheet3


Now, the "Quantity" information in the second order have to added (incrementally) in the “Database Sheet”:

updated database:
Book1
ABCDE
1DatabaseSheet
2QuantityTypeNameMetals
32ringwalkgoldsilver
43braceletstarsilver
54ringmerlingold
63ringcamaleongold
73braceletsnakesilver
83ringbirdgold
Sheet4


Thanks.

EDIT: Removed IMG Tags, they're unnecessary with the HTML Maker code - Moderator
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
milt2004,

You may be able to exploit Excel's SUM() formula if you manage the order sheets right. Put this formula in the Quantity sheet where the numbers you want to sum are: =SUM(Sheet1:Sheet3!A5)

Now, if you always add new order sheets between Sheet1 and Sheet3, the formula will automatically pick up the new numbers without any alteration of the formula.

Dufus
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Hi milt2000

How do you identify the unique item?

Type? Type+Name? Type+Name+Metals?
 

milt2004

New Member
Joined
Sep 4, 2004
Messages
28
Hi milt2000

How do you identify the unique item?

Type? Type+Name? Type+Name+Metals?

You'r right!
I forget to report this. So, in my catalogue, the identity for unique item is the code (a simple number that are unique). I send a sample of my catalogue.
Book10
ABCDEFG
1OrderSheet
2CodeNumberQuantityTypePictureNameMetals
312ring2ventaglisilver
421ringfilodeformesilver
531ringfogliesilver
651ringmodulosilver
Sheet1
 

milt2004

New Member
Joined
Sep 4, 2004
Messages
28

ADVERTISEMENT

I want also to clarify that the Sheet Order and the Database Order are not in the same workbook.
The Sheet Order, come from a Template and the Database Order is a simply sheet that, every time I use the Template for a new order, I want to collect the informations in the Databese Order (something like new totals - please, see one more time the example with the sheet and the database order)
Thanks.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
milt2004

assuming:
1) the sheet layout is exactly the same as you have posted
2) both books are open
Code:
Sub test()
Dim dic As Object, a, i As Long, wsDB As Worksheet, wsOrd As Worksheet
Dim ii As Long, w()
Set dic = CreateObject("Scripting.Dictionary")
Set wsDB = ThisWorkbook.Sheets("Sheet1")
Set wsOrd = Workbooks("OrderWorkbookNameHere").Sheets("Sheet1")
With wsDB.Range("a1").CurrentRegion
   If .Rows.Count > 2 Then
      a = .Resize(.Rows.Count-2,.Columns.Count).Offset(2).Value
      For i = 1 To UBound(a,1)
         If Not IsEmpty(a(i,1)) And Not dic.exists(a(i,1)) Then
            ReDim w(1 To UBound(a,2))
            For ii = 1 To UBound(a,2) : w(ii) = a(i,ii) : Next
            dic.add a(i,1), w
         End If
      Next
   Else
      wsOrd.Rows("1:2").Copy .Rows("1:2")
   End If
   With wsOrd.Range("a1").CurrentRegion
      a = .Resize(.Rows.Count-2,.Columns.Count).Offset(2).Value
      For i = 1 To UBound(a,1)
         If Not IsEmpty(a(i,1)) Then
            If Not dic.exists(a(i,1)) Then
               ReDim w(1 To UBound(a,2))
               For ii = 1 To UBound(a,2) : w(ii) = a(i,ii) : Next
               dic.add a(i,1), w
            Else
               w = dic(a(i,1))
               w(2) = w(2) + a(i,2)
               dic(a(i,1)) = w
            End If
         End If
      Next
   End With
   y = dic.items : Set dic = Nothing : Erase a
   With .Range("a" & Rows.Count).End(xlUp).Offset(1)
      For i = 0 To UBound(y)
         .Offset(i).Resize(,UBound(y)).Value = y(i)
      Next
   End With
End With
Set wsDB = Nothing : Set wsOrd = Nothing
End Sub
 

Forum statistics

Threads
1,141,628
Messages
5,707,499
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top