Combining data from multiple tables/sheets into final one

You may get replies quickly if you upload a sample excel file with input and expected result (Output)

Thanks

Hi there... and welcome to the Excel Forum. One way to do this is to assemble a list of order numbers that meet your criterion (Order value >0) - on sheet "helper" and consolidate that into a single list, on sheet "Master" column C, which you can then use to lookup the values that you want. The formula used to conslidate that list is an array formula. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

I also created a named range called tbl (CTRL F3) to make the referencing easier. There are a few areas in this sheet that would need to be tidied up; but before I spend any more time on it... is it close to what you want?

How many sheets of data are there going to be (currently you have 3...).

How many rows per sheet will you need?


I'll then finish the job for you.

I fiddled with this for a while and my end result was a bit slow. So, one rethink later: here it is. Any problems encountered, or explanations needed - just shout!!