Using DGET to extract multiple records

[Guest]

Is there a way to use DGET or a UDF to extract multiple records that match
the same criteria? Using a macro would be my 3rd choice.
Thanks in advance for any help on this.
Bob

 

[Guest]

Have you looked at Data->Filter->Advanced Filter?

 

[Guest]

Yes, but I'm looking for an automatic way (i.e., using a built-in function or
UDF).

 

[T. Valko]

How many rows of data need to be searched? How about some details.

Biff

 

[Harlan Grove]

Yes, but I'm looking for an automatic way (i.e., using a built-in
function or UDF).

....

Something like the following to get the _i_th row mathcing your
criteria and the _j_th column in that row.

=INDEX(Table,SMALL(IF(Criteria,ROW(Table)-MIN(ROW(Table))+1),i),j)

Table is a token reference to the table of data you're trying to
filter. Criteria is a placeholder for the criteria expression you want
to use.

 

[Guest]

The option Harlan gave you requires you to fill a range with formulas, and in
order to be sure you get EVERY SINGLE row, you must use a range of formulas
AT LEAST large enough for every possible row.

Alternatively, you can treat the data range as a database, use the
Data->Import External Data->New Database Query to create a dynamic query that
automatically updates every time you change the criteria. Excel takes care
of wiping out the previously retrieved values and updating with the values
meeting your newly entered criteria.

 

[Guest]

About 400 rows need to be searched.

How many rows of data need to be searched? How about some details.

Biff

 

[T. Valko]

About 400 rows need to be searched.

Ok......what are we searching for and where is it?

Biff

 

[Guest]

Duke,
Thanks for your help! Is there a way I can create a dynamic query that
automatically updates every time I change the underlying data (rather than
the criteria)?
Bob

 

[Guest]

Bob -

Off the top of my head I can't think of any way that Excel would know the
underlying data has changed. If the data gets changed by virtue of manual
input, you could set up a worksheet change event on the data sheet that
refreshes the query. Alternatively, if you are importing a data file, and
you have automated that, you could add a query refresh to the end of the VBA
routine(s) that import the data file.

Beyond that I have no ideas.

 

[Harlan Grove]

Off the top of my head I can't think of any way that Excel would
know the underlying data has changed. . . .

....

If the underlying data is in a worksheet range, any changes would
trigger recalculation. Figure it out from there.