# Auto populate from 1 worksheet to 2nd worksheet.

#### jram23

##### New Member
Hello All
Not sure what direction to go or how to phrase but looking for the following.

When a quantity is inputted into worksheet 1 (labeled: product), that quantity and item description will auto populate onto worksheet 2 (labeled: totals). Worksheet 2 should not have any quantities if worksheet 1 is zero.

WORKSHEET 1: Product
 Bottles Product ID# Units \$ Per Case Quantity Coke 34566 24 \$12.00 Coke Zero 36563 24 \$13.00 Diet Coke 63563 24 Diet Dr. Pepper 65654 24 \$14.00 Dr. Pepper 65456 24 \$15.00 Fanta Orange 56564 24 \$16.00 Ginger Ale 45675 24 \$20.00

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Worksheet 2: Totals

 Bottles Product ID# Total QTY

<colgroup><col><col><col></colgroup><tbody>
</tbody>
Easiest way I can describe it is it will be similar to shopping cart, but not used as one. I hope this can be accomplished with a formula as the primary users will have basic excel knowledge and limited web access. Primary focus for me would be formula assistance I can alter to my needs just unsure what direction to take it. Any help will do, thanks for the assistance.

#### BrianGGG

##### Board Regular
If you make worksheet 1 into a table called "t_Product", the following formulas can do what (I think) you are asking for:

BCD
15BottlesProduct ID#Quantity
16Coke345661
17Ginger Ale456753
18Fanta Orange565642
19
20
21
22
23

</tbody>
Summary

Worksheet Formulas
CellFormula
B16=IF(C16<>"",INDEX(t_Product[Bottles],MATCH(C16,t_Product[Product ID'#],0)),"")
C16=IF(ROWS(C\$16:C16)<=COUNT(t_Product[Quantity]),AGGREGATE(15,6,t_Product[Product ID'#]/IF(ISNUMBER(t_Product[Quantity]),1,0),ROWS(C\$16:C16)),"")
D16=IF(C16<>"",INDEX(t_Product[Quantity],MATCH(C16,t_Product[Product ID'#],0)),"")

</tbody>

<tbody>
</tbody>

Columns B and D are just INDEX/Match or table lookups based on the value of column C.

For Column C:
There is an array function for all of the Product IDs
These are divided by 1 if there is a quantity or 0 if there is not (causing an error for the rows with no quantity
The AGGREGATE function includes the parameter to ignore errors
The SMALL function of the AGGREGATE is used to get the legitimate Product IDs one by one
The ROWS function increments the SMALL number as you go down the rows

I hope this helps ...

BrianGGG

Last edited:

#### BrianGGG

##### Board Regular
Also, for those with EXCEL Insider edition, the following formula should work with Dynamic Array functions:

BCD
28Coke345661
29Fanta Orange565642
30Ginger Ale456753

</tbody>
Summary

Worksheet Formulas
CellFormula
B28=FILTER(CHOOSE({1,2,3},t_Product[Bottles],t_Product[Product ID'#],t_Product[Quantity]),ISNUMBER(t_Product[Quantity]))

</tbody>

<tbody>
</tbody>

#### mumps

##### Well-known Member
You may want to try a simple macro which would automate the process. The primary users would not need to have any knowledge of Excel. If you are willing to give it a try, copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Product" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a quantity in column E and exit the cell. The data will be automatically copied to the "Totals" sheet.

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
Range("A" & Target.Row).Resize(1, 2).Copy Sheets("Totals").Cells(Sheets("Totals").Rows.Count, "A").End(xlUp).Offset(1, 0)
Sheets("Totals").Cells(Sheets("Totals").Rows.Count, "C").End(xlUp).Offset(1, 0) = Target
End Sub``````

#### jram23

##### New Member
Thanks for the response and assistance. When I input a number it creates a line on the totals page; however, if you go back and change that number it creates a new line but keeps the original. Is there a way to "refresh" the totals without adding a new line item? Thanks again

#### jram23

##### New Member
Thanks BrianGGG

Non VBA formula-wise this is the direction I am looking; however, I think I'm getting the syntax incorrect. It is not giving me results.

To help me understand...in the formula provided, "C16" is the cell the worksheet is using to reference the quantities from worksheet 1 correct? So on my worksheet it would be "C2"?

Thanks again for you help and assistance.

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>
Summary

Worksheet Formulas
CellFormula
B16=IF(C16<>"",INDEX(t_Product[Bottles],MATCH(C16,t_Product[Product ID'#],0)),"")
C16=IF(ROWS(C\$16:C16)<=COUNT(t_Product[Quantity]),AGGREGATE(15,6,t_Product[Product ID'#]/IF(ISNUMBER(t_Product[Quantity]),1,0),ROWS(C\$16:C16)),"")
D16=IF(C16<>"",INDEX(t_Product[Quantity],MATCH(C16,t_Product[Product ID'#],0)),"")

<tbody>
</tbody>

<tbody>
</tbody>

Columns B and D are just INDEX/Match or table lookups based on the value of column C.

For Column C:
There is an array function for all of the Product IDs
These are divided by 1 if there is a quantity or 0 if there is not (causing an error for the rows with no quantity
The AGGREGATE function includes the parameter to ignore errors
The SMALL function of the AGGREGATE is used to get the legitimate Product IDs one by one
The ROWS function increments the SMALL number as you go down the rows

I hope this helps ...

BrianGGG[/QUOTE]

#### BrianGGG

##### Board Regular
This type of formula is very hard to cut/paste and it's very tempermental if things aren't exactly lined up.
Here's a sample file that might do a better job of answering your questions:

If you are still having problems, please post the data and the formula that you are using and I can take a look ...

BrianGGG

#### mumps

##### Well-known Member
Assuming that the values in column A ("Bottles") in Sheet1 are unique and there are no duplicates in that column, this macro should do what you want.
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
Dim bottles As Range
Set bottles = Sheets("Totals").Range("A:A").Find(Cells(Target.Row, 1).Value, LookIn:=xlValues, lookat:=xlWhole)
If Not bottles Is Nothing Then
Sheets("Totals").Cells(bottles.Row, 3) = Target
Else
Range("A" & Target.Row).Resize(1, 2).Copy Sheets("Totals").Cells(Sheets("Totals").Rows.Count, "A").End(xlUp).Offset(1, 0)
Sheets("Totals").Cells(Sheets("Totals").Rows.Count, "C").End(xlUp).Offset(1, 0) = Target
End If
End Sub``````

1,081,708
Messages
5,360,781
Members
400,595
Latest member
T_Dubs

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...