Consolidating and discriminating 2 sheets of data into a third:

[Reddberet]

Hello.

I am struggling with the following task, and although I am comfotable with basic excel formulas, I think this may require something a little more advanced:

re Consolidating and discriminating 2 sheets of data into a third:

I have 3 sheets of data:
1) a list of current open departments across the business, with dept numbers listed down column A, column B giving 4 row headings of Sales This Week This Year, This week last year, YTD this year, and YTD last year for each dept in column A, the headers across the top run from column C to column A1 giving the store locations.This lists every dept ever open in the business.

2) the second sheet lists only the depts that are currently open, with the stores listed across the top and the dept numbers down in column A. Each open dept has the word 'open' in its cell. This dept list does not contain every dept on the first page, only those relevant to having been open at some point in the last year

3) The third sheet needs to pull the YTD this year sales data from the first page but only for those depts on the 2nd page, it also needs to be able to cope with departments being 'closed' and 'opened' as the year progresses which will extend the the data on both the first and second sheet.

Can you help, as I'm not sure where to begin?
I think I'll need something that says, if you find the dept number on sheet 2, cell A1, in column A on sheet 1, put the values from the rest of the row in sheet 1 in sheet 3, together with the dept number. This needs to repeat until the last dept number on sheet 2 has been checked.

Phew! My thanks to anyone who fancies tackling this!

[watersev]

hi, Reddberet, it would be helpful to see a sample workbook