Windows 7 - GETPIVOTTABLE incompatibility between WIN EXCEL 03 & MAC EXCEL 08

Asked By John_Golfi
16-Mar-10 03:33 AM
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?
MAC Office Excel Discussions
 
Excel 2003
(1)
Excel 2007
(1)
Excel
(1)
Word
(1)
VBA
(1)
Webcrossing.JaKIaxP2ac0
(1)
MacBook
(1)
Windows
(1)
  John McGhie replied to John_Golfi
16-Mar-10 07:01 AM
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



On 16/03/10 6:33 PM, in article 59bb551e.-1@webcrossing.JaKIaxP2ac0,


--

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:john@mcghie.name
  John_Golfi replied to John_Golfi
16-Mar-10 10:58 PM
Hi John <br><br>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. <br><br>Regards <br>
JG
  John McGhie replied to John_Golfi
17-Mar-10 03:18 AM
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.

On 17/03/10 1:58 PM, in article 59bb551e.1@webcrossing.JaKIaxP2ac0,


Well, if you flip the commands on the Mac, it will work on Windows.  If you
do not flip the commands, it will not 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:john@mcghie.name
Previous Discussion:  Formatting in Excel
Create New Account
help
Copy and paste the last 50 or so lines into your reply, please. MowGreen [MVP 2003-2007] = = = = = = = = = = = = = = = *-343-* FDNY Never Forgotten = = = = = = = = = = = = = = = I have had the same problem, I run Windows Vista on or so lines of my update log off my computer, any assistance would be appreciated. 2007-08-01 16:15:03:371 4796 12e0 Handler : WARNING: Command line install completed. Return code = 0x00000643, Result = Failed, Reboot required = false 2007-08-01 16:15:03:397 4796 12e0 Handler : WARNING: Exit code = 0x8024200B 2007-08-01 16:15:03:397 4796 12e0 Handler ::::::::: 2007-08-01 16:15:03:397 1160 1098 AU > > ## RESUMED ## AU: Installing update [UpdateId = {46BEF854-E66A-4106-96B4-4F404CFE7563}] 2007-08-01 16:15:03:397 4796 12e0 Handler :: END :: Handler: Command Line Install 2007-08-01 16:15:03:397 1160 1098 AU # WARNING: Install failed, error = 0x80070643 / 0x00000643
then copy and paste the last 50 or so lines into your reply. MowGreen [MVP 2003-2008] = = = = = = = = = = = = = = = *-343-* FDNY Never Forgotten = = = = = = = = = = = = = = = having the same problem, I'll post my windows update the log issues from today! *This is part 1 post - my post is too long 2007-11-30 01:44:45:968 3456 5d44 Misc = = = = = = = = = = = Logging initialized (build: 7.0.6000.381, tz: -0600) = = = = = = = = = = = 2007-11-30 01:44:45:984 3456 5d44 Misc = Process: C: \ Program Files \ Windows Defender MSASCui.exe 2007-11-30 01:44:45:984 3456 5d44 Misc = Module: C: \ WINDOWS \ system32 \ wuapi.dll 2007-11-30 01:44:45:968 3456 5d44 COMAPI - -- -- -- -- -- -- 2007-11-30 01:44:45:984 3456 5d44 COMAPI - - START - - COMAPI: Search [ClientId = Windows Defender] 2007-11-30 01:44:45:984 3456 5d44 COMAPI 2007-11-30 01:45:16:031 3456 5d44 COMAPI FATAL: Unable to connect to the
Windows 7 Pivot tables in spreadsheets created inWindows version of Excel 2007 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 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
the steps to highlight the duplicte numbers in each row. My directions are for xcel 2007 and guide me to a: highlight the column of cells (e.g.; A2 to A21 Your assignment is referring to features available only in the most recent Windows versions of Excel (2007 / 2010). Conditional Formatting, itself, is available from the Format menu in the Mac version but it is comparable to Windows Excel 2003 & prior versions. You may be able to accomplish the task but an expression would have option. It may require a macro, however, and if it does Office 2008 has no VBA. Perhaps someone more experienced than I can offer a suggestion. Regards | :> ) Bob Jones [MVP] Office 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