auto populating table based on condition

[ialexander03]

I have a table of invoices on sheet 1, to which I want to automatically populate the table on sheet 2. However, only the one what have not been paid.


So, if a Y appears in column E, that row is not copied over.


Sounds simple, probably is, however, its needs to be a "living" table, so when an invoice is paid, and the Y is put on sheet 1, it is removed from the table on sheet 2, and everything shifts up, leaving no gaps etc.


Hope that makes sence.

Sheet 1
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Client Ref Number[/TD]
[TD]invoice number[/TD]
[TD]Invoice Date[/TD]
[TD]invoiced amount[/TD]
[TD]invoice paid[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]CUS1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl78, width: 90"]01/01/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl66, width: 90, align: right"] $4,534.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]CUS2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl78, width: 90"]18/01/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl66, width: 90, align: right"] $67,246.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]CUS3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl78, width: 90"]15/01/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl66, width: 90, align: right"] $13,453.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]CUS4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl78, width: 90"]28/01/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl66, width: 90, align: right"] $136.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]CUS5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl78, width: 90"]28/02/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl66, width: 90, align: right"] $34,636.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]CUS6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl78, width: 90"]18/03/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl66, width: 90, align: right"] $6,346.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]CUS7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]7[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl78, width: 90"]20/03/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl66, width: 90, align: right"] $88,785.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]CUS8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl78, width: 90"]28/03/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl66, width: 90, align: right"] $362.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]CUS9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl78, width: 90"]28/03/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl66, width: 90, align: right"] $263,564.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2, what I want anyway, is the same as above, but without CUS1, CUS4, CUS6 and CUS8 as they have the paid marker, but should I enter the paid marker (on the live table on sheet 1) into CUS7, for CUS7 to auto remove from the sheet 2 table, and everything move up.

and yes, I know this may sound a pointless task, as you just use auto filter on the invoice paid column on sheet 1, but the original is a much larger document, with more fields than in this example, and sheet 2, will form part of a printable report page, hence why i want it to be auto populating!

 

[sunnyland]

Hello there,

I am thinking of a simple way using Microsoft Query which you can find [I am using Office Standard 2013] under:

Data
From Other Sources
From Microsoft Query

You can then choose the columns you want to include in Sheet2 and the criteria you want to apply to the returning data. You can use it in a workbook from data on different sheet.

You just will have to refresh before printing [there is option to refresh on opening and every timing you choose but better to refresh before printing, less resources consuming]
In most recent version of Excel, there may be something better.

In case you are not familiar with it: https://support.office.com/en-us/ar...nal-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e

Let me know if this is helpful.