Autofilter when value in a cell changes

[streamofmight]

Hi

username: username1
password: password1

There are a list of transactions under "Transactions Listing"
There can be items that fall into the same invoice number.
Under "Invoice", I would like to show all the transactions that occurred that belong to a particular input invoice number.


How do I do that? Please assist. Do note that the target array in "Transactions Listing" needs to be dynamic so that when new entries are made, it can reflect the changes. Thank you!

If excel functions can be used, that would be great too!

[TMS]

To make a dynamic named range for the transactions, I'd use:

Transaction: ='Transaction Listing'!$A1048571:INDEX('Transaction Listing'!$N:$N,COUNTA('Transaction Listing'!$B:$B))

Normally, I'd use the key column (Column A, in this case) but your data is offset for one of the entries (row 8) so I've used column B. It actually generates a larger range than I had anticipated due to the formula in column B.

You'd be better defining the transaction data as a Table because formatting and formulae will automatically be copied to new rows.


You need a Worksheet Change event monitoring the Invoice Number:

Please Login or Register  to view this content.

That runs an Advanced Filter when the WSC event is fired. To make this work, you need to match the headings in the transaction list to those in the Invoice, or vice versa.

And you really need to provide an invoice list that can be used in Data Validation in cell C4.


See your example workbook updated.


Regards, TMS

[streamofmight]

Thanks for the help! Where have you put your named range "Transactions" by the way?

[TMS]

It's a dynamic named range so you'll need to look in Name Manager.

Regards, TMS

[streamofmight]

Hi

as usual username: username1
password = password1

I have tried to make a new sheet, called "Statement of Accounts" and use the same thing that you have shown me to work on it.
I was able to get it to work. But could I add a secondary filter in the yellow box where i can filter the results by the Customer Name and also the month?

Thanks.

You have been really helpful. Many thanks.

[streamofmight]

Sorry you would have to type in "Customer Name" in sheet "Standard of Account", cell A1

[TMS]

As well as changing the number of fields you want to input, you have changed the layout.

Unfortunately, the approach adopted on the original solution requires cells C3 and C4 to contain "Invoice Number" as a heading and the actual invoice number respectively. That's a requirement of the Advanced Filter.

If you want to filter on more fields, you need to set up the criteria range somewhere. When you change the first criteria, you need to clear the second (and subsequent) criteria and the filtered output. When you select the second criteria, assuming there are no other criteria, you then run the Advanced Filter (in the Worksheet Change event).

So, for example, if you have three criteria, when you change the first, you clear two and three and the output. If you just changed criteria two, you'd clear three and the output. And, when you change the third criteria, you run the Advanced Filter.

I'm happy to offer advice but I do not have the time to develop the application for you.

Regards, TMS