Retrieving bulk data

[dstrad]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a workbook with 12 sheets (Jan – Dec), designed to record all purchase orders that I issue. Each sheet has 10 columns:<o></o>
(<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">PO</st1lace>#, vendor, description, cost code#, equipment#, job#, requested by, total cost, date, and location).<o></o>
<o> </o>
Is there a way to collect certain info on a separate worksheet or workbook like such?<o></o>
Example:
List certain (or all) data for equipment# 2-9940, from each sheet, in sequential order.<o></o>
Basically I would like to be able to enter the criteria that I want or select the equipment # from an in-cell dropdown list and have each row of data that has that particular equipment #, list sequentially by date (or position on worksheet).<o></o>
<o> </o>
I am familiar with retrieving data using in-cell dropdown menus and vlookup, but I’m not sure how to retrieve bulk data.<o></o>
<o> </o>
Hope this makes sense.<o></o>
Thanks in advance<o></o>

 

[Hermanito]

One possible way would be:
- link all 12 sheets as linked tables in an Access database file
- create a union query that combines all 12 tables
- add a Query Table in Excel that gets its data from the Access Query
- Apply an autofilter on the Query table, making it easy to filter the view

Disadvantages:
- requires Access
- not very easy to set-up if you have never seen or done it before

Advantages:
- no formulas or named ranges to manage in Excel
- once it's set-up, all the Access things are done transparently on the background

 

[dstrad]

Thanks Hermanito,
I'll try reading and researching your suggestion. It's sounds a bit difficult but that's all I have to go on at this point.

Thanks

 

[Hermanito]

Feel free to ask questions in this thread if you run into problems... I will try to help you if I can. I have set-up such things before, but it's too long a process to describe in one post.