Creating a dynamically populated table based on criteria

[Fishboy]

Hi all,

I am looking to create a new table in my Excel 2010 workbook which dynamically populates with data from the other sheets within the workbook based on a certain criteria.

To cut a long story short, across various sheets are a huge number of different products which are being bought and sold at different prices. For each item on each individual sheet I already have a formula which works out whether the price is good or bad and offers a recommended action based on the prices.

What I am trying to achieve is to build a master table which populates with only products that have a value of "Bargain!" in column L from a pre-defined list of lookup sheets, or alternatively the entire workbook. This check needs to be dynamic as the sales and purchase prices of items change almost daily, so a price is not always going to be set as a "Bargain!".

There are more complex things I would also like this to be able to do at a later date, however if I can get a working prototype based on the above it would give me a starting point and allow me to decide what else needs to be recorded.

For arguments sake, the sheet I am building the new table on is currently just called 'Sheet1', but all of the other sheets in the workbook have names. Would I be better of searching the whole workbook in it's entirety or is there a way to created a range of sheets? Either way, does anyone know of a way to write the formula that will do this?

To give a simplified example, the formula would look on sheet 'Material Costs' in column L for the value "Bargain!", then for every time it finds a positive match to this search criteria it populates the data of that entire row into the new table on 'Sheet1' from cell B2 onwards.

 

[Fazza]

hello, Fishboy

A parameterised query can do what you've described. It requires no formulas & no code: can pull from multiple worksheets.

Suggest you google for examples. Such as by searching for : Excel 2010 parameterised query tutorial

OK?

 

[Fishboy]

Thanks Fazza,

Now I know what to Google, I'm sure I can find an answer. Cheers mate!

 

[Fazza]

You're welcome, Fishboy. Please setup & try a few examples. They will likely be for data from one source only. Though there will be plenty of old forum posts where I've described data from multiple sources.

For data from multiple sources, define the joining/combining of the data with SQL. Read enough web sites or ask questions to gain understanding. Simplest might be like this

SELECT some fields
FROM one_table
UNION ALL
SELECT same fields
FROM another_table
UNION ALL
SELECT fields again
FROM yet_another_source_etc

cheers