Sorting and copying automatically between worksheets

[nadia]

I have a list of tasks for a team which includes start dates, names and a
couple of other criteria.

Is it possible to create a sepatate workbook which looks at the first one
and selects the rows which correspond to a particular name or start date etc?

Also if it can be done, can it be linked so that it automatically updates if
someone changes information in one document?

If it can be done, could you tell me how?

Cheers

N

 

[flummi]

Your table in the original worksheet:

team project departm start end
Michael p1 sales 12.05.2006 30.09.2006
Peter p2 lab 01.04.2006 30.04.2006
Susan p3 accounts 01.05.2006 31.12.2006

select the entire table including the headers and name the range in the
name box
save the workbook

Create a new workbook
In a1 type "members"
In B1 type %

Click A3 (the first cell of your query)
Select data-->get external data-->new database query
on the next screen select "excel files"
click ok
On the file open dialogue navigate to the main workbook and double
click it
On the next screen you will see the the name you gave your data table
click it and then click the right arrow next to the box
That will include all the field names of your table in the right hand
box
click next
define any selection criteria on the next screen or none and hit next
define any sort criteria on the next screen or none and hit next
click "view data or edit query" on the next screen and hit finish
that will bring up the Query screen
select "view-->criteria to display the selection criteria pane
click the first field in the row named "Criteria Field" and select e.g.
"team"
click the field right below this field named "Value"
type: like[team%]
click outside this field
click "cancel" on the next screen
click the "return data" icon next to "SQL"
On the next screen click "Parameters"
Select "get value fromn the following cell" and click ok
click the aquare icon at the right in the box below the radio button
On your worksheet click B1
click the square icon in your reference selection field which will
return to the previous screen
click "Refresh automatically when cell value changes"
click ok
on the next screen click "Properties"
on the next screen under "data formatting and layout" deselect
everything except "include field names"
Select "Insert entire rows for new data; clear unused cells"
check "fill down formulas in columns adjacent to data"
click ok
on the next screen click ok
At this stage you will see on row 3 the column headers of your original
table
Your field for the selection criterion "team member" is B1.
Type "%" which means "everything"
When you hit return you will see the data of your original table.
Similarly if you enter "michael" in B1 you will only see Michael
If you enter Mi% you will see all names beginning with "mi"

Save your workbook

Hope it works as you require.

Hans

 

[nadia]

Thank you so much for your help - i will try it out today and let you know
how it goes

Cheers

N