VLOOKUP formula for multiple worksheets

[jem-jems]

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!!

 

[Ashish Mathur]

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]

Ashish

many thanks for responding to my post. ive followed your instructions but i
get stuck at point 10, as it requests that i manually create the
relationships between the tables ..

do you have an email address that i can send my spreadsheet to you so you
can see what i mean?

many thanks again for your help!!!!

jem-jems

 

[Ashish Mathur]

Hi,

Click on OK on that box

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Hi,

If you wish, you may mail the file to me at ask(at)ashishmathur(dot)com.
Please mail a small file I.e. mail the relevant information only. Also,
please state the question clearly.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com