Populate table based on data in another

**Walshy2851** · 10-10-2015, 08:54 AM

Hi there,

I was hoping somebody might be able to help me with an issue I have.

My spreadsheet has two tables, one of which includes an entire list of transactions. The second only contains some of the transactions from the main table i.e. it does not have any transactions that do not appear in the 'main' table.

Up until now, the second table has been populated by copying and pasting certain entries from the main table to it but I am trying to automate this process.

Both tables start with the same column headers. They are as follows:

Date Invoice Number Customer Country Description Net Value Per Invoice Currency Exchange Rate Net USD Value Code

The first table starts in Cell A13 and the second starts in A20. The second table is actually the main table. I am trying to achieve the following:

- The column entitled 'Code' has a drop down list to select a code for the transaction.
- The list of codes is stored in a separate sheet called Codes
- There are 22 codes in the list. If any of 13 of the 22 are selected, I want the entire row to be populated in the table which started at cell A1.
- The codes are in F2:F23 of the sheet entitled Codes. It is the last 13 of these in a row which I have referred to above.
- Ideally I would like this to automatically update without even running a macro. Perhaps I could build in a refresh.all macro that runs anytime a cell is changed to trigger this.
- I have no way of knowing how many entries will be in either table so I need the smaller table to just add a row each time the criteria is met.
- So effectively, I want a live duplicated table which is filtered...

Any ideas?

Thanks in advance!

**Mumps1** · 10-10-2015, 09:07 AM

It sounds like you need a Worksheet_Change event which would trigger the action automatically after selecting a code. It would be much easier to help if we could see your actual file. Could you post a copy?

**Walshy2851** · 10-10-2015, 09:26 AM

Thanks for the quick reply. I have attached a very rough template which will be updated once I have found a workaround for what I am trying to achieve.

Any help would be greatly appreciated!

**Mumps1** · 10-10-2015, 01:05 PM

Just a little clarification: Do you want the row from the table starting in A13 or from the table starting in A20 to be populated starting at A1? You want this to happens when a code from F11:F23 on the Codes sheet is selected from the drop down list. Is this correct?

**Walshy2851** · 10-10-2015, 01:17 PM

Originally Posted by Mumps1

Just a little clarification: Do you want the row from the table starting in A13 or from the table starting in A20 to be populated starting at A1? You want this to happens when a code from F11:F23 on the Codes sheet is selected from the drop down list. Is this correct?

I would like the table which starts in A13 to be populated based on the data in the table which starts in A20. Correct, I would like it to populate only when one of the codes in F11:F23 of the Codes sheet is chosen. To complicate matters, I do not know how many rows will be entered at the outset, so I would need the table starting at A13 to add a row, when necessary. Hope that makes sense! Thanks

**Mumps1** · 10-10-2015, 01:47 PM

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Workings" 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. Make your code selection. Keep in mind that you should enter the data in all the other columns before selecting your code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("J:J")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim codesArray As Variant
    Dim code As Range
    For Each code In Sheets("Codes").Range("F11:F23")
        If Target = code Then
            Rows(14).Insert
            Target.EntireRow.Copy Rows(14)
            Exit For
        End If
    Next code
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

**Mumps1** · 10-12-2015, 02:07 PM

Click this link to download the file. https://app.box.com/s/0173p1qn0ll8dsayvij30hjdm37xr5ii Remember to insert a unique value in column Q each time you start a new row. Also, I've eliminated the need for a helper cell in cell BB1.

Populate table based on data in another

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Populate table based on data in another

Re: Populate table based on data in another

Re: Populate table based on data in another

Re: Populate table based on data in another

Re: Populate table based on data in another

Re: Populate table based on data in another

Re: Populate table based on data in another

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Populate data on table based on multiple criteria and value selected from drop-down

[SOLVED] Trying to auto-populate based on if one column matches data from another table

Populate data in a table based on dropdown criteria

[SOLVED] Automatically populate a table with slected data based on criteria

Macro to select data based in a search term and populate a table

[SOLVED] Populate Table with data from other sheets based on drop down selection

Populate table with data based on another sheet

Bookmarks

Bookmarks

Posting Permissions