REFERENCE A WKSHT FROM ANOTHER WHILE FILTERING/QUERYING

[PrgrmsAll]

Is there a way that I can treat a worksheet in Excel like a database table and then query it? For example, I have two worksheets in a workbook. wksA and wksB. In wksA(A1) i want to insert a count of the number of records in wksB that have the value '01' in the LOCATION column and the value '500' in the SUPPLIER column. In a database query I would simply do a "SELECT COUNT(*) from wksB WHERE LOCATION = '01' AND SUPPLIER = '500';"

Any advice would be helpful. I should add that I want to avoid actually having to import wksB into Access or some other DB. Also, wksA(A1) is the first cell in a 600 cell matrix that will need to run queries on wksB's data.

 

[PHV]

You may create a query on the spreadsheet with MS-Query:
menu Data -> Get external Data -> Create a query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[taupirho]

Look at the EXCEL DCOUNT function. With a little tweaking this will do what you want.

 

[PrgrmsAll]

Well, not sure if it will. I my source is actually a spreadsheet, not a database. Do I have to create a DSN pointing to the spreadsheet and then create the query that way?