OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

GETPIVOTDATA ?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Aladdin
General User
General User


Joined: 23 Jan 2007
Posts: 40
Location: France

PostPosted: Tue May 06, 2008 8:48 am    Post subject: GETPIVOTDATA ? Reply with quote

Hi there,

I was wondering if it's possible to get a value from pivot table not in current spreadsheet ?
For example :
I'm on first.ods I want to get value from second.ods pivot table ?

In A1 first.ods cell :
=GETPIVOTDATA("Sum - Sales";'file:///C:/TMP/second.ods'#$'Sheet1'.$A$1;"Field";"Value";"Field2";"Value2")

=> get #REF

Thanks for your help, best regards.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 06, 2008 10:31 am    Post subject: Reply with quote

The reference imports plain data only. So there is no pivot table in the hidden sheet named 'file:///C:/TMP/second.ods'#$'Sheet1. Have a look at Menu:Format>Sheets>Show...
If there is no such sheet it might be due to the third apostrophe in 'file:///C:/TMP/second.ods'#$'Sheet1'....
Anyway, GETPIVOTDATA can not work with external pilots.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
AndrewZ
Moderator
Moderator


Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA

PostPosted: Tue May 06, 2008 3:50 pm    Post subject: Reply with quote

c.f,
someone filed an enhancement request


"GETPIVOTDATA doesn't allow to reference a data pilot in another document"
http://qa.openoffice.org/issues/show_bug.cgi?id=89078
_________________
<signature>
* Did you solve your problem? Do others a favor: Post the solution
* OpenOffice.org Ninja
* BleachBit
</signature>
Back to top
View user's profile Send private message Visit poster's website
Aladdin
General User
General User


Joined: 23 Jan 2007
Posts: 40
Location: France

PostPosted: Tue May 06, 2008 11:25 pm    Post subject: Reply with quote

OOps sorry for the typo my mistake Wink

Ok thanks for your advice.


Last edited by Aladdin on Wed May 07, 2008 3:15 am; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed May 07, 2008 2:04 am    Post subject: Reply with quote

What you can do with the given tools:
File>New>Database...
[X]Connect to existing database, type: Spreadsheet ...
[X]Register database
Save database file.
Now this pseudo-database displays the sheets and database ranges of the source spreadsheet as read-only database tables. Among other useful things you can create pivot tables from the same source data without importing the source range.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Aladdin
General User
General User


Joined: 23 Jan 2007
Posts: 40
Location: France

PostPosted: Wed May 07, 2008 3:16 am    Post subject: Reply with quote

Good idea, I'm gonna test thanks Wink
Back to top
View user's profile Send private message
mbrouillet
General User
General User


Joined: 06 May 2010
Posts: 13

PostPosted: Thu May 13, 2010 11:49 pm    Post subject: Reply with quote

Villeroy wrote:
Anyway, GETPIVOTDATA can not work with external pilots.


Does that apply to PilotData that comes from a registered database with an SQL query ?
Would you then recommend to use INDEX and MATCH instead (assuming this would work) ?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group