Auto populate from 1 worksheet to 2nd worksheet.

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
BottlesProduct ID#Units$ Per CaseQuantity
Coke3456624$12.00
Coke Zero3656324$13.00
Diet Coke6356324
Diet Dr. Pepper6565424$14.00
Dr. Pepper6545624$15.00
Fanta Orange5656424$16.00
Ginger Ale4567524$20.00

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



Worksheet 2: Totals

BottlesProduct 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
Joined
Mar 5, 2016
Messages
59
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><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)),"")

<thead>
</thead><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
 
Last edited:

BrianGGG

Board Regular
Joined
Mar 5, 2016
Messages
59
Also, for those with EXCEL Insider edition, the following formula should work with Dynamic Array functions:


BCD
28Coke345661
29Fanta Orange565642
30Ginger Ale456753

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</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]))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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
Joined
Dec 22, 2018
Messages
3
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
Joined
Dec 22, 2018
Messages
3
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
Joined
Mar 5, 2016
Messages
59
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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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
 

Forum statistics

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

Some videos you may like

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