| View previous topic :: View next topic |
| Author |
Message |
carpman
General User
Joined: 23 Jul 2003
Posts: 15
|
 Posted: Wed Jul 23, 2003 11:56 am Post subject: datapilot - confused |
 |
|
Hello, i am confused about datapilot, i think it what i need to do what i want but can't get it to work.
I have setup a spreadsheet, 1 sheet is for the data and 1 sheet for creating summery of data. I have setup the data sheet as datasource, it has columsn such as:
Mem Code Family Name First Name Alias Ethnic Code Contact No. Mobile Sex
now i want to take some of these columns and display them on summery page with ability to sort as needed. I have tried using datapilot but cannot get data to display as i want, ie column headers with data under them.
Anyone have any ideas or know where i can find a good howto on datapilot?
cheers |
|
| Back to top |
|
 |
carpman
General User
Joined: 23 Jul 2003
Posts: 15
|
| Posted: Wed Jul 23, 2003 1:20 pm Post subject: |
|
|
Ok, managed to get data sorted but if i change datasource and do datapilot refresh it does not work.
any ideas |
|
| Back to top |
|
|
dfrench
Moderator
Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand
|
| Posted: Thu Jul 24, 2003 2:00 am Post subject: |
|
|
There is a good section in the HELP ... start at Datapilot, Guide
If you are starting with your data in a spreadsheet, you do not need to create a datasource to point to the spreadsheet, just select the spreadsheet data and then go menu>data>data pilot>start. The result is a pivot table.
If you put the cursor in the pivot table you can refresh the table by using menu>data>data pilot>refresh |
|
| Back to top |
|
|
carpman
General User
Joined: 23 Jul 2003
Posts: 15
|
| Posted: Tue Jul 29, 2003 3:50 pm Post subject: |
|
|
| dfrench wrote: | There is a good section in the HELP ... start at Datapilot, Guide
If you are starting with your data in a spreadsheet, you do not need to create a datasource to point to the spreadsheet, just select the spreadsheet data and then go menu>data>data pilot>start. The result is a pivot table.
If you put the cursor in the pivot table you can refresh the table by using menu>data>data pilot>refresh |
Hello, thanks for reply.
Looked in help but it is not that clear to me 
Ok tried your way, yep get nice pivot table but only get it in the same sheet as i selected data in, want to put it on another sheet.
Tried defining infomation via 'menu,data,define range' and selecting this for datapilot but no go.
Got things sort of working with external spreadsheet and datasource but could never get external data to update |
|
| Back to top |
|
|
dfrench
Moderator
Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand
|
| Posted: Wed Jul 30, 2003 12:04 am Post subject: |
|
|
| Quote: | Looked in help but it is not that clear to me
Ok tried your way, yep get nice pivot table but only get it in the same sheet as i selected data in, want to put it on another sheet.
|
May I recommend the current version of OOo this answers your question exactly in the HELP
| Quote: | Selecting DataPilot Output Ranges
Click the button More in the DataPilot dialog. The dialog will be extended.
You can select a named range in which the DataPilot table is to be created, from the Results to box. If the results range does not have a name, enter the coordinates of the upper left cell of the range into the field to the right of the Results to box. You can also click on the appropriate cell to have the coordinates entered accordingly.
If you mark the Ignore empty rows check box, they will not be taken into account when the DataPilot table is created.
If the Identify categories check box is marked, the categories will be identified by their headings and assigned accordingly when the DataPilot table is created. |
If you have particular difficulties, please specify the version and OS that you are working with. |
|
| Back to top |
|
|
carpman
General User
Joined: 23 Jul 2003
Posts: 15
|
| Posted: Wed Jul 30, 2003 4:08 am Post subject: |
|
|
Thanks for reply, ok using lastest version now.
Got it working in different sheet, but have another problem. The data in question is club membership, an example of problem is this: Column A is Contact Number, now if a number is entered in a row and no other numbers are entered in following rows, in the datapilot sheet the following rows are filled with last entered number.
datat sheet =
Contact Number:
row 1 01020304040
row2
row3
Datapilot sheet =
Contact Number:
row 1 01020304040
row2 01020304040
row3 01020304040
Another problem is formula in data sheet. In data sheet one of the colomns is date of birth, i have setup another column to get current age of member. Now if DOB is not entered then the number 103 (years since 1900) appears in age cell, would like to set this so if no DOB entered then age cell would be empty.
Also if i enter a new record/row in data sheet then when datapilot sheet is refreshed it does not show. So haw can i setup datapilot so when new record added to data sheet it is also added to datapilot shhet?
Related to this is how can i set up cells in datasheet so that new records will inhenrit formula, IE age formula. I tried the smart fill, but the i get the previous mentioned problem of number 103 being entered, also this is lmited by number of cells i smart fill.
Apologies about the amount of questions but i am new to spreadsheets, i am reading help files but can't always relate examples to what i am trying to do.
many thanks[/b] |
|
| Back to top |
|
|
carpman
General User
Joined: 23 Jul 2003
Posts: 15
|
| Posted: Wed Jul 30, 2003 7:49 am Post subject: |
|
|
Hello, ok sorted adding new record, need to select grater range then actual has data in to allow for for new records, though this does limit records to range selected for data pilot which may be problem as i won't have control of spreadsheet when it is finished and goes out to clubs.
Just need to sort other issues now  |
|
| Back to top |
|
|
dfrench
Moderator
Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand
|
| Posted: Thu Jul 31, 2003 12:16 am Post subject: |
|
|
| Quote: | | now if a number is entered in a row and no other numbers are entered in following rows, in the datapilot sheet the following rows are filled with last entered number |
If you have empty cells in the source for a column. Outting that column label as a column in the pivot should result in (empty) appearing as one of the values. Can't replicate this problem.
| Quote: | would like to set this so if no DOB entered then age cell would be empty.
|
Use IF in the formula like =IF(A1="";"";YEARS(A1;NOW();1))
| Quote: | So haw can i setup datapilot so when new record added to data sheet it is also added to datapilot shhet?
|
For this you need to set the data up as an external data source. This is quite a challenge for the newcomer to OOo and we all are! Well worth searching this forum for stuff about treating calc sheets as a database or OOo registered datasource. Plenty of problems in this area. If you are developing a solution where you have your analysis or summary in the pivot table and the data source is maintained elsewhere as a rows and columns of data then I suggest that you consider a simple database. That can also be used as the source for datapilot. |
|
| Back to top |
|
|
carpman
General User
Joined: 23 Jul 2003
Posts: 15
|
| Posted: Thu Jul 31, 2003 3:42 am Post subject: |
|
|
| dfrench wrote: |
If you have empty cells in the source for a column. Outting that column label as a column in the pivot should result in (empty) appearing as one of the values. Can't replicate this problem. |
Yes it does that so long as there are not entries in column soon as i enter something in column all following column cells are filled with that entry?
| Quote: |
Use IF in the formula like =IF(A1="";"";YEARS(A1;NOW();1))
|
I am using =YEARS(O2;TODAY();0) will try your suggestion.
| Quote: |
For this you need to set the data up as an external data source. This is quite a challenge for the newcomer to OOo and we all are! Well worth searching this forum for stuff about treating calc sheets as a database or OOo registered datasource. Plenty of problems in this area. If you are developing a solution where you have your analysis or summary in the pivot table and the data source is maintained elsewhere as a rows and columns of data then I suggest that you consider a simple database. That can also be used as the source for datapilot. |
I did setup sheet in another spreadsheet file as datasource but could not get datapilot table to update when source was edited.
Using database is going to be difficult as i won't be the only one using file, i am trying to develop this for number of youth clubs to add membership tacking and monthly monitoring reports so finished file will be distributed to number of clubs.
Will look into it as i will be installing openoffice on the computers i am getting ready for them. |
|
| Back to top |
|
|
|
