Dynamic cell range for functions
 

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

Dynamic cell range for functions
 

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
|