Say the sheet has these headers, with maybe 100 - 150 + or so rows of data.

PO_No. Invoice Page Line Ref Description Qty (Column A - G)

Users will not be trusted to use Auto Filter from the sheet to do the following.

A user has Invoice, Line & Qty info. Find all rows that match those three criteria, return Entire Row Data to sheet 2. Where Entire Row Data is from column A to G.

Next user has Invoice, Ref, Description & Qty. Now find/return all rows that match those four items to sheet 2. (Below last entry)

A user has only the Invoice. Return all rows with a Invoice value.

Can code work with a variable number of "Filter By" choices, especially with choices that will be different almost every time as well as the number of header choices?

Or an InputBox would be okay for the users. I think the order of the headers and the order of the data to the InputBox is not really important, right?

Howard

Hi Howard,

Am Sun, 22 Nov 2015 12:57:45 -0800 (PST) schrieb L. Howard:

Say the sheet has these headers, with maybe 100 - 150 + or so rows of data.

PO_No. Invoice Page Line Ref Description Qty (Column A - G)

Users will not be trusted to use Auto Filter from the sheet to do the following.

A user has Invoice, Line & Qty info. Find all rows that match those three criteria, return Entire Row Data to sheet 2. Where Entire Row Data is from column A to G.

Next user has Invoice, Ref, Description & Qty. Now find/return all rows that match those four items to sheet 2. (Below last entry)

A user has only the Invoice. Return all rows with a Invoice value.

Can code work with a variable number of "Filter By" choices, especially with choices that will be different almost every time as well as the number of header choices?

Or an InputBox would be okay for the users. I think the order of the headers and the order of the data to the InputBox is not really important, right?

that is a case for a advanced filter. Create the criteria range on
sheet2. And with Worksheet_Change Event you can fire this filter when
users enter the criteria into the criteria range.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Say the sheet has these headers, with maybe 100 - 150 + or so rows of
data.

PO_No. Invoice Page Line Ref Description Qty (Column A - G)

Users will not be trusted to use Auto Filter from the sheet to do the
following.

A user has Invoice, Line & Qty info. Find all rows that match those
three criteria, return Entire Row Data to sheet 2. Where Entire Row
Data is from column A to G.

Next user has Invoice, Ref, Description & Qty. Now find/return all
rows that match those four items to sheet 2. (Below last entry)

A user has only the Invoice. Return all rows with a Invoice value.

Can code work with a variable number of "Filter By" choices,
especially with choices that will be different almost every time as
well as the number of header choices?

Or an InputBox would be okay for the users. I think the order of the
headers and the order of the data to the InputBox is not really
important, right?

Howard

Adding to Claus' suggestion.., this can be better handled using ADODB
so you can programmatically specify recordset criteria for each lookup.
Once the recordset is created its entire contents can be 'dumped'
anywhere into a worksheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Monday, November 23, 2015 at 9:30:04 AM UTC-8, GS wrote:
Say the sheet has these headers, with maybe 100 - 150 + or so rows of
data.

PO_No. Invoice Page Line Ref Description Qty (Column A - G)

Users will not be trusted to use Auto Filter from the sheet to do the
following.

A user has Invoice, Line & Qty info. Find all rows that match those
three criteria, return Entire Row Data to sheet 2. Where Entire Row
Data is from column A to G.

Next user has Invoice, Ref, Description & Qty. Now find/return all
rows that match those four items to sheet 2. (Below last entry)

A user has only the Invoice. Return all rows with a Invoice value.

Can code work with a variable number of "Filter By" choices,
especially with choices that will be different almost every time as
well as the number of header choices?

Or an InputBox would be okay for the users. I think the order of the
headers and the order of the data to the InputBox is not really
important, right?

Howard

Adding to Claus' suggestion.., this can be better handled using ADODB
so you can programmatically specify recordset criteria for each lookup.
Once the recordset is created its entire contents can be 'dumped'
anywhere into a worksheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

The end users will be very low level Excel savvy. Going with a userform with labeled text boxes for entries.

Thanks, Howard

The end users will be very low level Excel savvy. Going with a
userform with labeled text boxes for entries.

Sounds like a perfect scenario for using ADODB!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I have no idea what ADODB is myself.

Howard

I have no idea what ADODB is myself.

Howard

Hmm! Regardless of what you provide end users with for a UI, the data
being processed requires code!

ADODB is the database tool to use for building filtered recordsets
where...

Worksheets and ranges are data tables
Workbooks are databases
Data is managed via queries

Here's a primer for using database programming in Excel...

http://www.appspro.com/conference/Da...rogramming.zip

...that includes full instructions for various needs.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Mon, 23 Nov 2015 20:30:59 -0500 schrieb GS:

http://www.appspro.com/conference/Da...rogramming.zip

can you please download the workbook "Advanced Filter" from
https://onedrive.live.com/redir?resi...=folder%2cxlsm
and test the macro? If possible in xl2013. For me it works fine but
Howard has some problems with this workbook.

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Mon, 23 Nov 2015 20:30:59 -0500 schrieb GS:

http://www.appspro.com/conference/Da...rogramming.zip

can you please download the workbook "Advanced Filter" from
https://onedrive.live.com/redir?resi...=folder%2cxlsm
and test the macro? If possible in xl2013. For me it works fine but
Howard has some problems with this workbook.

Regards
Claus B.

I do not have 2013, but I can see how it works in 2010...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Wed, 25 Nov 2015 10:45:22 -0500 schrieb GS:

I do not have 2013, but I can see how it works in 2010...

thank you. I hope you find the mistake which makes the code not run on
Howard's machine.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Wed, 25 Nov 2015 10:45:22 -0500 schrieb GS:

I do not have 2013, but I can see how it works in 2010...

thank you. I hope you find the mistake which makes the code not run
on Howard's machine.


Regards
Claus B.

The macro works fine but I have no idea how to validate/verify the
results...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Wed, 25 Nov 2015 10:58:17 -0500 schrieb GS:

The macro works fine but I have no idea how to validate/verify the
results...

thank you. That is what I want to hear.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional