Auto populate from 1 worksheet to 2nd worksheet.

J

jram23

New Member
Joined
Dec 22, 2018
Messages
3
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
[TABLE="width: 497"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Bottles[/TD]
[TD]Product ID#[/TD]
[TD]Units[/TD]
[TD]$ Per Case[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Coke[/TD]
[TD="align: right"]34566[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]$12.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Coke Zero[/TD]
[TD="align: right"]36563[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]$13.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Diet Coke[/TD]
[TD="align: right"]63563[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Diet Dr. Pepper[/TD]
[TD="align: right"]65654[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]$14.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dr. Pepper[/TD]
[TD="align: right"]65456[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]$15.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fanta Orange[/TD]
[TD="align: right"]56564[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]$16.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ginger Ale[/TD]
[TD="align: right"]45675[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]$20.00 [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Worksheet 2: Totals

[TABLE="width: 381"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Bottles[/TD]
[TD]Product ID#[/TD]
[TD]Total
QTY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
If you make worksheet 1 into a table called "t_Product", the following formulas can do what (I think) you are asking for:

BCD
BottlesProduct ID#Quantity
Coke
Ginger Ale
Fanta Orange

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]34566[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]45675[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]56564[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

</tbody>
Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B16[/TH]
[TD="align: left"]=IF(C16<>"",INDEX(t_Product[Bottles],MATCH(C16,t_Product[Product ID'#],0)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C16[/TH]
[TD="align: left"]=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)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D16[/TH]
[TD="align: left"]=IF(C16<>"",INDEX(t_Product[Quantity],MATCH(C16,t_Product[Product ID'#],0)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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:
Upvote 0
Also, for those with EXCEL Insider edition, the following formula should work with Dynamic Array functions:


BCD
Coke
Fanta Orange
Ginger Ale

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]28[/TD]

[TD="align: right"]34566[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]56564[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]45675[/TD]
[TD="align: right"]3[/TD]

</tbody>
Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B28[/TH]
[TD="align: left"]=FILTER(CHOOSE({1,2,3},t_Product[Bottles],t_Product[Product ID'#],t_Product[Quantity]),ISNUMBER(t_Product[Quantity]))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
[TABLE="width: 412"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

<tbody>
[TD="align: center"][/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: center"][/TD]

[TD="align: center"][/TD]

[TD="align: center"][/TD]

[TD="align: center"][/TD]

</tbody>
Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B16[/TH]
[TD="align: left"]=IF(C16<>"",INDEX(t_Product[Bottles],MATCH(C16,t_Product[Product ID'#],0)),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C16[/TH]
[TD="align: left"]=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)),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D16[/TH]
[TD="align: left"]=IF(C16<>"",INDEX(t_Product[Quantity],MATCH(C16,t_Product[Product ID'#],0)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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]
 
Upvote 0
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:


Here's a Link


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



BrianGGG
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top