GETPIVOTTABLE incompatibility between WIN EXCEL 03 & MAC EXCEL 08

[John_Golfin]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I recently bought a MacBook Pro 15.4, 3.06GHz, 8GB RAM running Mac Excel 2008 (v12.2.3 091001). When I use Mac Excel 2008 to open ".XLS" excel files that I created in Windows Excel 2003 (11.6560.6568) SP2, all my pivot tables convert correctly but any REFERENCES to any fields within the pivot tables via GETPIVOTDATA do NOT show the valid values that Win Excel showed via EXACTLY THE SAME COMMAND. Instead in Mac Excel 08 all such references have "#VALUE!" in the cell. There is a valid reference startement there but no valid value. This is clearly a bug since I have made NO CHANGES to the excel spreasheet and when I view a file saved as XLSX in Mac Excel 08 back on a Windows Laptop you can see the original values. How can I fix this?

 

[John McGhie]

Hi John:

Yeah, it is indeed a bug: the syntax is backwards.

The function GetPivotData exists in both Excel for Mac (04 and 08), and in
the Windows version (03, 07 and 10).

However, while the function performs the same task on both systems, the
syntax is different. The Windows syntax is
GETPIVOTDATA(data_field,pivot_table)

(http://office.microsoft.com/en-us/excel/HP100624771033.aspx)

The Mac syntax is:
GETPIVOTDATA(pivot_table,name) where name refers to the same parameter as
data_field above:

(http://www.microsoft.com/mac/help.mspx?target=9e985a8c-3d6c-4886-9137-d6b82
3e504a71033&clr=99-1-0)

As you can see, the syntax is reversed on the Mac, which causes spreadsheets
to break when viewed on a Mac.

Excel 2007 can use the reverse syntax of the Mac version, so it is
possible to create a pivot table that will work on both platforms, by
flipping the formula parameter values on the Mac. When you send it back to
Windows, it should work...

Cheers

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
I recently bought a MacBook Pro 15.4, 3.06GHz, 8GB RAM running Mac Excel 2008
(v12.2.3 091001). When I use Mac Excel 2008 to open ".XLS" excel files that I
created in Windows Excel 2003 (11.6560.6568) SP2, all my pivot tables convert
correctly but any REFERENCES to any fields within the pivot tables via
GETPIVOTDATA do NOT show the valid values that Win Excel showed via EXACTLY
THE SAME COMMAND. Instead in Mac Excel 08 all such references have "#VALUE!"
in the cell. There is a valid reference startement there but no valid value.
This is clearly a bug since I have made NO CHANGES to the excel spreasheet and
when I view a file saved as XLSX in Mac Excel 08 back on a Windows Laptop you
can see the original values. How can I fix this?

--

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]

 

[John_Golfin]

Hi John

With respect, how do you "flip the formula parameter values on the Mac" for hundreds of cells on a number of spreadsheets. I cannot do this manually. Also, I do not need to flip commands for Win Excel since it already works. I need to flip the commands for Mac.

Regards
JG

 

[John McGhie]

Well, you have access to Windows Excel, so you COULD write a tricky little
macro to edit the formulas for you. Probably quicker to do it by hand,
unless you are an expert VBA coder.

Also, I do not need to flip commands for Win Excel since it already works. I
need to flip the commands for Mac.

Well, if you flip the commands on the Mac, it will work on Windows. If you
do not flip the commands, it won't work on the Mac.

Don't shoot me, I am not from Microsoft

Cheers

--

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]