How to return multiple instances of data from multiple worksheets

[fzeke150]

I have data in multiple worksheets in which each row has a cell that tells the 'destination' for the supplies in that row. The row also includes unit cost, quantity and total cost. I want to be able to pull all rows across all worksheets that have have the same 'destination' into a new worksheet.

The data sheets are set up in the following fashion....

So if I want a new worksheet to show all supplies for the Front Porch across multiple worksheets in this same layout (other than the 'Destination' column) how would I build my formula? I played with the =if(Countif but never was able to succeed. Thanks!

Data from A4 to E7

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet title
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Material
[/TD]
[TD]Unit Cost
[/TD]
[TD]Quantity Needed
[/TD]
[TD]Total Cost
[/TD]
[TD]Destination
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2x4
[/TD]
[TD]3.00
[/TD]
[TD]2
[/TD]
[TD]6.00
[/TD]
[TD]Front Porch
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]pipe
[/TD]
[TD]2.00
[/TD]
[TD]4
[/TD]
[TD]8.00
[/TD]
[TD]Kitchen
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]paint
[/TD]
[TD]5.00
[/TD]
[TD]1
[/TD]
[TD]5.00
[/TD]
[TD]Front Porch
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brush
[/TD]
[TD]4.00
[/TD]
[TD]3
[/TD]
[TD]12.00
[/TD]
[TD]Bathroom
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ca.moazzam]

Hi what do you need to have on the result sheet, I mean all data relating to the required destination or that destination's number of rows or its total cost etc.?

Moazzam

 

[fzeke150]

Hi what do you need to have on the result sheet, I mean all data relating to the required destination or that destination's number of rows or its total cost etc.?

Moazzam

Moazzam,

Thanks for your post. I am trying to return the first 4 columns to all rows that match the 'destination' that I decided to use for that worksheet. For example 'Front Porch' for the first worksheet. So I would like to have all rows that have 'Front Porch' as 'Destination' to report all their respective columns so that my output array looks the same as the imput array OTHER THAN HAVING A "Destination" column.

I hope this makes sense.

Thanks