Hi,

Try this:

1. Select the data range on the Actions sheet and assign it a name
(Ctrl+F3), say actions. When selecting the range for name assignment,
please select the header row. If there is no header for the 2 columns,
please create a header row (say, Date and Status) and use the same headings
on the three other sheets
2. Repeat step 1 for ranges on the other three sheets. The names assigned
should be issues, risks and plan
3. Save the file (try.xls) on the desktop
4. Create anew sheet and now go to Data Import External Data New
Database query
5. Select Excel files and click on OK
6. Browse to the desktop (in the right hand side pane), select try.xls (in
the left hand side pane) and click on Next
7. In the Choose column box, select Actions and click on the greater than
symbol.
8. Repeat step 7 for the other names as well
9. Click on Next and in the box, press OK
10. Click on the SQL button and type

Select * from actions
union all
select * from issues
union all
select * from risks
union all
select * from plan

Click on Finish

11. This will be one consolidated list of data spread across all sheets
12. Go to file Return Data to MS Office Excel
13. In the import data box, select any cell where you want the output
14. Now you can filter the Status column on 'In Progress'

If you edit data on any sheet, you just ned to right click and select
Refresh on the any cell of the output range.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"jem-jems" wrote in message
...
Hello

I have a workbook with 4 worksheets within it (ACTIONS, ISSUES, RISKS,
PLAN). Each worksheet's first column is 'Due Date' and the second column
is
'Status' (with either 'completed' or 'in progress' in each cell).

I want to have add a SUMMARY worksheet tab that summarises all the cells
within the workbook that have tasks in progress. i.e. the 'Status' columns
from the ACTIONS, ISSUES, RISKS, PLAN worksheets to be shown in one
worksheet; only where the status is 'in progress' (not completed), and to
show the 'due date' and any other relevant information from each row, from
each worksheet.

basically instead of going to each worksheet tab and filtering by 'in
progress' and sorting the due date order, I will be able to see all
outstanding tasks in one summary sheet.

Does anyone know the formula (VLOOKUP i presume) that will show (in the
summary worksheet) all tasks in progress with the due date in ascending
order?

ive done this before but i cant remember it, it was something about naming
the range and locking cells with the $ symbol (if you dont use it you
loose
it!!)

many thanks!!