Add incremental information from a sheet to another

[milt2004]

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
	A	B	C	D	E
1	(first)OrderSheet
2	Quantity	Type	Name	Metals
3	1	ring	walk	gold	silver
4	2	bracelet	star		silver
5	1	ring	merlin	gold
6	1	ring	camaleon	gold
7	2	bracelet	snake		silver
8	1	ring	bird	gold
Sheet1

first database:

Book1
	A	B	C	D	E
1	(first)DatabaseSheet
2	Quantity	Type	Name	Metals
3	1	ring	walk	gold	silver
4	2	bracelet	star		silver
5	1	ring	merlin	gold
6	1	ring	camaleon	gold
7	2	bracelet	snake		silver
8	1	ring	bird	gold
Sheet2

second order:

Book1
	A	B	C	D	E
1	(second)OrderSheet
2	Quantity	Type	Name	Metals
3	1	ring	walk	gold	silver
4	1	bracelet	star		silver
5	3	ring	merlin	gold
6	2	ring	camaleon	gold
7	1	bracelet	snake		silver
8	2	ring	bird	gold
Sheet3

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

updated database:

Book1
	A	B	C	D	E
1	DatabaseSheet
2	Quantity	Type	Name	Metals
3	2	ring	walk	gold	silver
4	3	bracelet	star		silver
5	4	ring	merlin	gold
6	3	ring	camaleon	gold
7	3	bracelet	snake		silver
8	3	ring	bird	gold
Sheet4

Thanks.

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

 

[shajueasow]

HI,
Are you looking for
Sheet3!A3+1
in cell A3 of sheet4.

 

[milt2004]

HI,
Are you looking for
Sheet3!A3+1
in cell A3 of sheet4.

Sorry.
I'm afraid is not so simple...

 

[Dufus]

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]

Hi milt2000

How do you identify the unique item?

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

 

[milt2004]

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
	A	B	C	D	E	F	G
1	OrderSheet
2	CodeNumber	Quantity	Type	Picture	Name	Metals
3	1	2	ring		2ventagli	silver
4	2	1	ring		filodeforme	silver
5	3	1	ring		foglie	silver
6	5	1	ring		modulo	silver
Sheet1

 

[milt2004]

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.

 

[Dufus]

Did you try my suggestion?

Dufus

 

[jindon]

milt2004

assuming:
1) the sheet layout is exactly the same as you have posted
2) both books are open

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

 

[milt2004]

Did you try my suggestion?

Dufus

Hello Dufus.

Yes, I try your suggestion, but sorry, it is not exactly that I need.