Hi Rob,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Rob Hayles" wrote in message ...
| Hi,
|
| I have a problem that I can't seem to find any reference in books to help.
|
| The scenario is that I have a multi-sheet workbook, sheet 1 (Master Work
| List) contains the details of all the work that we have assigned, one row per
| task. One of the details in the task is the developer to whom the work is
| assigned. What I am then trying to do is make one sheet for each developer
| listing only work that is assigned to them.
|
| For each column on the employees sheet (starting at Col c) I have the
| following function
| =LOOKUP($G$2,'Master Work List'!$F3:$F$500,'Master Work List'!A3:A$500)
| This seems to work, so in G2 there is the Employee initial, and it searches
| "Master Work List' sheet to get the task back, however it only returns the
| first task for the employee.
|
| What I am trying to do is have the search list auto built from the contents
| of another cell (which I can then create based on the previous search), so
| each time I can start the search after the row of the last match.
|
| I have tried the following
| =LOOKUP($G$2,CELL("contents",A5),'Master Work List'!A3:A$500)
|
| where the contents of A5 are 'Master Work List'!$F5:$F$500
|
| Where A5 contains the new search list, but this does not work, with Excel
| saying 'Volatile'
|
| Has someone tried this before, is there a way to do this, am I simply mad?
|
| I would appreciate any comments to help
|
| Many Thanks
| Rob
|