Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting and copying automatically between worksheets
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting and copying automatically between worksheets
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting and copying automatically between worksheets
Thank you so much for your help - i will try it out today and let you know
how it goes Cheers N "flummi" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|