Sort/filter by editing a cell

**nohero** · 06-21-2011, 02:01 PM

I have example sheet that Charles and tigeravatar from this forum helped me put together here:

Example-3 (modified v2).xlsm

What I would like is when I input a serial number in cell C3 on the "Main" worksheet that it auto sorts or filters the rows in the "Orders" worksheet so that every entry related to the serial number ends up at the top. I can't just have the user sort them manually as these sheets will be hidden and the user will have no knowledge of how to navigate an Excel file.

The reason for this is my vlookup array formulae in my "Main" worksheet (B10:E13) will be set in 36 rows to return 36 results maximum. I need this because if I have it search the entire 65k rows it freezes my workbook for 20-30 seconds to process all the info. By sorting and then only searching the top 36 rows of "Orders" it should breeze through it in no time.

Make sense? Who can help?

I'd also be willing to listen to other ideas on how to accomplish this, always keeping in mind the end user needs this to be idiot-proof.

**Richard Buttrey** · 06-21-2011, 02:28 PM

Hi,

I'm presuming that you don't actually have thousands of rows on the Order sheet.
Why not just create a dynamic range name for the data on the Orders sheet and use that dynamic range name in your VLOOKUP().

And then why are you using a formula, and a SUMPRODUCT() formulae at that on the Main sheet? These are inherently slow once you get past a few dozen rows. Why not just use a straightforward Data Filter Advanced to extract the orders rows. I suspect that it's these that are likely slowing your system down not necessarily the VLOOKUPS.

Regards

**nohero** · 06-21-2011, 02:51 PM

Actually, there will in fact be thousands of entries in the "Orders" worksheet. Likely tens of thousands. For this reason, a dynamic range will grow large enough to cause the same problem I'm having now.

The formula for the array is one I found somewhere else and it was the shortest formula I could get that properly checked for errors and listed every instance of a serial number on the orders tab.

If you could maybe help me with a formula that will extract all the rows (the formula will need to search through tens of thousands of rows) beginning with the serial number I input and be able to have a blank cell on error, that would be very helpful. I just don't know how to do it any better.

**Richard Buttrey** · 06-21-2011, 05:39 PM

Hi,

As I said, you don't need formulae on the Main sheet to extract the relevant rows. Just use Data Filter Advanced with a criteria range - see the attached.

Regards

**nohero** · 06-22-2011, 11:37 AM

Yes, I considered having a macro to pull the relevant data to the bottom section of the main page but was hoping it could be avoided. My reason is this: if the person using the database pulls up a number and prints the page off for a product's history without hitting the filter macro first, this will cause incomplete data or even data from the previously viewed product if the macro was not re-triggered.

I suppose the solution would be to incorporate the filter into the print macro I will have on the page.

This is a possibility but I'm still curious if there's a way to auto-filter upon entering a serial number in cell C3 that does not require triggering a macro. It truly cuts down on the potential for error.

Any thoughts?

**nohero** · 06-27-2011, 10:05 AM

Any more advice out there?

**nohero** · 06-28-2011, 12:35 PM

Finally found a solution on a separate forum using:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Did exactly what I wanted. Thank you Richard for your contribution to the thread, I actually used your filter code in my final solution so it was indeed a better way to pull the info from the other sheet.