Pivot tables in spreadsheets created inWindows version of Excel 2007

[FD]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When you open spreadsheets incorporating pivot tables originally created in Excel 2007 for Windows the numbers turn to Ref! and Value! error messages. Having bought a new Mac and a new copy of Excel 2008, having previously had the same problem with Excel 2003 for Mac, I was expecting this aspect to be compatible. Does anybody know what the fix is?

 

[John_McGhie_[MVP]]

First place to look: check there are no back-slashes in path names in the
formulas. On the Mac, they need to be forward slashes or colons.

"Ref!" means the formula is making a reference to an object that cannot be
found (i.e. The path is wrong)

"Value!" probably means the value is Zero because the prerequisite was not
found.

Hope this helps

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
When you open spreadsheets incorporating pivot tables originally created in
Excel 2007 for Windows the numbers turn to Ref! and Value! error messages.
Having bought a new Mac and a new copy of Excel 2008, having previously had
the same problem with Excel 2003 for Mac, I was expecting this aspect to be
compatible. Does anybody know what the fix is?

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[FD]

Thanks for this. However I don't think this applies to my problem. Having looked into it again I think it is due to the differing syntax on the GETPIVOTDATA function between the Mac and Windows office versions so I don't think there is a fix.

Regards

Frances

 

[Bob Greenblatt]

Thanks for this. However I don't think this applies to my problem.
Having looked into it again I think it is due to the differing syntax on
the GETPIVOTDATA function between the Mac and Windows office versions so
I don't think there is a fix.

Regards

Frances

Well, yeah, there is no getpivotdata function in Mac Excel 2008 as there
is no VBA. You are right, there is no fix. Wait till the next version of
Mac Office.

 

[CyberTaz]

Hi Bob;

Well, yeah, there is no getpivotdata function in Mac Excel 2008 as there
is no VBA. You are right, there is no fix. Wait till the next version of
Mac Office.

I really don't mean to contradict, but that fx most definitely is available
in Excel 2008... And it works It appears that the conclusion Frances
came to is the source of the problem - there is a definite difference in the
syntax requirements for Mac Excel's implementation of the =GETPIVOTDATA() fx
if you go beyond the most basic level of the expression. Example:

Pivot Table sums quantity sold for a number of products whose IDs are the
PT's column headings. The Rows are based on three years [1999, 2000, 2001]
subdivided by months. 'Sum of Sales' in A5, 'Jan' in A7, '2000' in B8

Mac expr for Jan total in year 2000: =GETPIVOTDATA(A5,"Jan 2000")
Win expr for the same result : =GETPIVOTDATA(A5,A7,B8)

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

Hi Bob;

Well, yeah, there is no getpivotdata function in Mac Excel 2008 as there
is no VBA. You are right, there is no fix. Wait till the next version of
Mac Office.

I really don't mean to contradict, but that fx most definitely is available
in Excel 2008... And it works It appears that the conclusion Frances
came to is the source of the problem - there is a definite difference in the
syntax requirements for Mac Excel's implementation of the =GETPIVOTDATA() fx
if you go beyond the most basic level of the expression. Example:

Pivot Table sums quantity sold for a number of products whose IDs are the
PT's column headings. The Rows are based on three years [1999, 2000, 2001]
subdivided by months. 'Sum of Sales' in A5, 'Jan' in A7, '2000' in B8

Mac expr for Jan total in year 2000: =GETPIVOTDATA(A5,"Jan 2000")
Win expr for the same result : =GETPIVOTDATA(A5,A7,B8)

Regards |:>)
Bob Jones
[MVP] Office:Mac

Sorry my bad. My foot is out of my mouth now.

 

[CyberTaz]

Hey Bob;

Hi Bob;

On 5/24/10 7:58 AM, (e-mail address removed) wrote:
Thanks for this. However I don't think this applies to my problem.
Having looked into it again I think it is due to the differing syntax
on
the GETPIVOTDATA function between the Mac and Windows office versions
so
I don't think there is a fix.

Regards

Frances
Well, yeah, there is no getpivotdata function in Mac Excel 2008 as there
is no VBA. You are right, there is no fix. Wait till the next version of
Mac Office.

I really don't mean to contradict, but that fx most definitely is
available
in Excel 2008... And it works It appears that the conclusion Frances
came to is the source of the problem - there is a definite difference in
the
syntax requirements for Mac Excel's implementation of the =GETPIVOTDATA()
fx
if you go beyond the most basic level of the expression. Example:

Pivot Table sums quantity sold for a number of products whose IDs are the
PT's column headings. The Rows are based on three years [1999, 2000,
2001]
subdivided by months. 'Sum of Sales' in A5, 'Jan' in A7, '2000' in B8

Mac expr for Jan total in year 2000: =GETPIVOTDATA(A5,"Jan 2000")
Win expr for the same result : =GETPIVOTDATA(A5,A7,B8)

Regards |:>)
Bob Jones
[MVP] Office:Mac

Sorry my bad. My foot is out of my mouth now.

Does that mean I get to score one "Gotcha" point?

 

[John_McGhie_[MVP]]

Damn! Sorry!! I forgot about the syntax difference.

Really hard to talk with my foot in my mouth...

Thanks for this. However I don't think this applies to my problem. Having
looked into it again I think it is due to the differing syntax on the
GETPIVOTDATA function between the Mac and Windows office versions so I don't
think there is a fix.

Regards

Frances

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[pjonesCET]

Hey Taz that's two points both John and Bo are extracting feet. ;-)

 

[John_McGhie_[MVP]]

Hi Phillip:

Now we have our feet out of our mouths, we're gonna use 'em to come and GET
you!!

Frances, aren't you glad to have the opportunity to join a truly adult
discourse?

Cheers

Hey Taz that's two points both John and Bo are extracting feet. ;-)

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[pjonesCET]

IF it all was seriousness, it would drive us crazy trying save all the lost Microsoft souls

 

[FD]

Thanks for your help. The trouble is that the whole point of this is to be able to accept spreadsheets from other people working on the Windows platform, tinker with them and send them back again. If I go round changing the syntax people using Windows won't be able to use them. In particular I can't bring a file home from work, work on it in the evening and send it back to myself to use the next day.

Cheers everyone

D

 

[pjonesCET]

One solution your not going to like, is buy a copy of Windows 7, Office 2010 for Windows, and Parallels. The you can use you Mac at home and still experience the joys of working on The PC Version.

 

[John_McGhie_[MVP]]

If you use the Mac syntax (the "old" syntax) it will work on Windows. If
they use the Windows syntax (the "new" syntax...) it will not work on the
Mac.

Cheers

Thanks for your help. The trouble is that the whole point of this is to be
able to accept spreadsheets from other people working on the Windows platform,
tinker with them and send them back again. If I go round changing the syntax
people using Windows won't be able to use them. In particular I can't bring a
file home from work, work on it in the evening and send it back to myself to
use the next day.

Cheers everyone

D

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]