Populate table based on data in another

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!

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!

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

Thank you very much for that, it's pretty close to perfect!

Just a few queries, if you don't mind:

1. Is it possible to trigger the above like a normal macro i.e. call the macro to run once I have finalised the data in the lower table? I ask this because it is possible that the entries in the lower table will change. And if they do, the original copied row will remain and would need to be deleted manually.

2. Is it possible that the inserted row will not include the values in columns O or P? So it would only copy the data in A to N inclusive.

Thanks again.

Originally Posted by Mumps1

There may be a better way to handle that situation and have the original copied row deleted automatically. This would be possible if each row in the lower table contains a unique identifying value, for example a unique Invoice Number in column B. Will each entry in the lower table have a different Invoice Number with no duplicates in that table?

No, unfortunately it is possible that the same invoice number could appear more than once. I could add a unique number for each row but keep it hidden if that helps?

Updated template attached. I have added an extra column to the far left which can be hidden but I have left it visible so you can see where I put it. It could also be in column A and would work too.

Thanks for that - it works until a new row is added in lower table then it seems to stop working. Any ideas?