software

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

Your table in the original worksheet:

teamprojectdepartmstartend
Michaelp1sales12.05.200630.09.2006
Peterp2lab01.04.200630.04.2006
Susanp3accounts01.05.200631.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 quot;membersquot;
In B1 type %

Click A3 (the first cell of your query)
Select data--gt;get external data--gt;new database query
on the next screen select quot;excel filesquot;
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 quot;view data or edit queryquot; on the next screen and hit finish
that will bring up the Query screen
select quot;view--gt;criteria to display the selection criteria pane
click the first field in the row named quot;Criteria Fieldquot; and select e.g.
quot;teamquot;
click the field right below this field named quot;Valuequot;
type: like[team%]
click outside this field
click quot;cancelquot; on the next screen
click the quot;return dataquot; icon next to quot;SQLquot;
On the next screen click quot;Parametersquot;
Select quot;get value fromn the following cellquot; 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 quot;Refresh automatically when cell value changesquot;
click ok
on the next screen click quot;Propertiesquot;
on the next screen under quot;data formatting and layoutquot; deselect
everything except quot;include field namesquot;
Select quot;Insert entire rows for new data; clear unused cellsquot;
check quot;fill down formulas in columns adjacent to dataquot;
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 quot;team memberquot; is B1.
Type quot;%quot; which means quot;everythingquot;
When you hit return you will see the data of your original table.
Similarly if you enter quot;michaelquot; in B1 you will only see Michael
If you enter Mi% you will see all names beginning with quot;miquot;

Save your workbook

Hope it works as you require.

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

Cheers

N

quot;flummiquot; wrote:

gt; Your table in the original worksheet:
gt;
gt; teamprojectdepartmstartend
gt; Michaelp1sales12.05.200630.09.2006
gt; Peterp2lab01.04.200630.04.2006
gt; Susanp3accounts01.05.200631.12.2006
gt;
gt; select the entire table including the headers and name the range in the
gt; name box
gt; save the workbook
gt;
gt; Create a new workbook
gt; In a1 type quot;membersquot;
gt; In B1 type %
gt;
gt; Click A3 (the first cell of your query)
gt; Select data--gt;get external data--gt;new database query
gt; on the next screen select quot;excel filesquot;
gt; click ok
gt; On the file open dialogue navigate to the main workbook and double
gt; click it
gt; On the next screen you will see the the name you gave your data table
gt; click it and then click the right arrow next to the box
gt; That will include all the field names of your table in the right hand
gt; box
gt; click next
gt; define any selection criteria on the next screen or none and hit next
gt; define any sort criteria on the next screen or none and hit next
gt; click quot;view data or edit queryquot; on the next screen and hit finish
gt; that will bring up the Query screen
gt; select quot;view--gt;criteria to display the selection criteria pane
gt; click the first field in the row named quot;Criteria Fieldquot; and select e.g.
gt; quot;teamquot;
gt; click the field right below this field named quot;Valuequot;
gt; type: like[team%]
gt; click outside this field
gt; click quot;cancelquot; on the next screen
gt; click the quot;return dataquot; icon next to quot;SQLquot;
gt; On the next screen click quot;Parametersquot;
gt; Select quot;get value fromn the following cellquot; and click ok
gt; click the aquare icon at the right in the box below the radio button
gt; On your worksheet click B1
gt; click the square icon in your reference selection field which will
gt; return to the previous screen
gt; click quot;Refresh automatically when cell value changesquot;
gt; click ok
gt; on the next screen click quot;Propertiesquot;
gt; on the next screen under quot;data formatting and layoutquot; deselect
gt; everything except quot;include field namesquot;
gt; Select quot;Insert entire rows for new data; clear unused cellsquot;
gt; check quot;fill down formulas in columns adjacent to dataquot;
gt; click ok
gt; on the next screen click ok
gt; At this stage you will see on row 3 the column headers of your original
gt; table
gt; Your field for the selection criterion quot;team memberquot; is B1.
gt; Type quot;%quot; which means quot;everythingquot;
gt; When you hit return you will see the data of your original table.
gt; Similarly if you enter quot;michaelquot; in B1 you will only see Michael
gt; If you enter Mi% you will see all names beginning with quot;miquot;
gt;
gt; Save your workbook
gt;
gt; Hope it works as you require.
gt;
gt; Hans
gt;
gt;