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.1.0 080409). When I use Mac Excel 2008 to open excel files that I created in Windows Excel 97 2004, all my pivot tables convert correctly but any REFERENCES to any fields within the pivot table do NOT show the valid values that Excel 97 showed. Instead all refrences have "#VALUE!" in the cell. There is a valid reference startement there but no valid value. How can I fix this?

0 |

3/9/2010 12:11:31 AM

You are a few updates behind: make sure your copy of Mac Excel is fully up-to-date. Also: Check the Excel KNOWN ISSUES Help topic. There is an issue with using GETPIVOTDATA in .XLSB format. Hope this helps On 9/03/10 11:11 AM, in article 59bb4586.-1@webcrossing.JaKIaxP2ac0, "John_Golfin@officeformac.com" <John_Golfin@officeformac.com> wrote: > 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.1.0 080409). When I use Mac Excel 2008 to open excel files that I created > in Windows Excel 97 2004, all my pivot tables convert correctly but any > REFERENCES to any fields within the pivot table do NOT show the valid values > that Excel 97 showed. Instead all refrences have "#VALUE!" in the cell. There > is a valid reference startement there but no valid value. How can I fix this? This email is my business email -- Please do not email me about forum matters 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

0 |

3/10/2010 3:23:27 AM

Thanks John. <br><br>I upgraded to the very latest Mac Excel version and still get the same problem. <br><br>Where do I find the "Excel KNOWN ISSUES Help topic"? <br><br>Regards <br> John Golfin

0 |

3/11/2010 6:59:56 AM

Hi John: It's in the Excel Help. Make sure you're looking at the EXCEL help, not the Mac Help :-) If you search in the EXCEL help for GETPIVOTDATA, it will be the second item. Basically, it says "This doesn't work if the file format is .XLSB". Hope this helps On 11/03/10 5:59 PM, in article 59bb4586.1@webcrossing.JaKIaxP2ac0, "John_Golfin@officeformac.com" <John_Golfin@officeformac.com> wrote: > Thanks John. > > I upgraded to the very latest Mac Excel version and still get the same > problem. > > Where do I find the "Excel KNOWN ISSUES Help topic"? > > Regards > John Golfin This email is my business email -- Please do not email me about forum matters 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

0 |

3/12/2010 1:25:53 AM

Hi John <br><br>I found the known issue you refer to and it does not apply to me. I also checked other issues and they also do not apply. I am surprised that no one else has reported this since this a highly visible error as you cannot see any refrenced value to a Pivot Table. <br><br>Has anyone else seen this? <br><br>Regards <br> John Golfin

0 |

3/16/2010 7:03:24 AM

Hi John, I can help further investigate this trouble, if you can send me (xinxin@microsoft.com) a copy of your workbook. <br><br>Thanks, <br> XinXin Liu <br> Test Lead, Macintosh Business Unit, Microsoft <br><br>> Hi John <br> > <br> > I found the known issue you refer to and it does not apply to me. I also checked other issues and they also do not apply. I am surprised that no one else has reported this since this a highly visible error as you cannot see any refrenced value to a Pivot Table. <br> > <br> > Has anyone else seen this? <br> > <br> > Regards <br> > John Golfin

0 |

3/16/2010 7:25:22 AM

See my response a moment ago: I suspect he has been hit by the reverse syntax bug. Cheers On 16/03/10 6:25 PM, in article 59bb4586.4@webcrossing.JaKIaxP2ac0, "XinXin@officeformac.com" <XinXin@officeformac.com> wrote: > Hi John, I can help further investigate this trouble, if you can send me > (xinxin@microsoft.com) a copy of your workbook. > > Thanks, > XinXin Liu > Test Lead, Macintosh Business Unit, Microsoft > >> Hi John >> >> I found the known issue you refer to and it does not apply to me. I also >> checked other issues and they also do not apply. I am surprised that no one >> else has reported this since this a highly visible error as you cannot see >> any refrenced value to a Pivot Table. >> >> Has anyone else seen this? >> >> Regards >> John Golfin -- 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

0 |

3/16/2010 11:03:04 AM

I have a SUMProduct formula that I wrote in VBA. It works fine until the combination in the criteria is such where there are 0 records, rather than it returning a 0, it says #Value, but I'm not sure how to deal with it. Does anyone have an idea?? Here is my code. Function Kountifs(mPositionC, mBeginDateC, mEndDateC, mEntityC) As Long Dim mPositionCriteria As String Dim mBeginDateCriteria As Variant Dim mEndDateCriteria As Variant Dim mTimeCriteria As String Dim mEntityCriteria As String Dim mQuestion1Criteria As String Dim mTimeRange As Range Dim mPositionRange As R...

i have a sheet set up with formulas in cells such a =SUM(A1*D1)+SUM(B1*E1), etc.. i want to have totals at the bottom of the columns but they won' produce totals for me when i sum(F1:F300) for instance, it only return a #VALUE! because there are some cells which don't have numbers. i code or something that will delete all cells which have #VALUE! i them, and keep all actual value cells -- chie ----------------------------------------------------------------------- chief's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=777 View this thread: http://www.exc...

HELP!!! Problem: receive #VALUE! Using a defined NAME range In workbook MonthlyBudget09Mstr.xlsx are sheets 1209Sch and 1209 1209Sch contains all monthly entries, 1209 is the summary sheet. Defined name: HousingItems, ='1209Sch'!$L$83:$L$88, Values: {"1";"2";"3";"4";"5";"15"} Workbook <<from Name Manager L88 is a SUM function but I have remove L88 from range and still have problem Placing =HousingItems in cell B4 of sheet 1209 results in #VALUE! Error B4:B9 are empty Can copy and then paste 1209Sch L8...

Hi I've downloaded a mortgage calculator from the web, being anewbie I'm stuck with the following issue: When the last payment comes down to 0 or near 0 like 0.03 cents all the formulas values return a #VALUE!, I want that the value be displayed as 0. In example the maximum payments are 431 monthly payments, lets say the last payment the client made was for 378, now after that paymet I need the value to be displayed as 0 up to row 431, because he has finished his payments. A formula is if(F378+D378<C378,C378-(D378+F378),"") EVALUATES TO------>...

Hello, How do I get rid of this error message from my cell -#Value!. I have a cell C1 which has a number for example 500 and C2 I have XXX in the cell and C3 I used this formula =C1-C2, but I get the #Value! error message. What is funny about this is on one worksheet cell C3 returns the number 500, but on another worksheet I get the error message. Why? and how do get the value? Any tips will be appreciated. Thank you in advance. Cheers The problem is that you're attempting to subtract a TEXT value from a numeric value. What do you want to happen when C2 contains a TEXT entr...

I have a column with formulas, and when there is no data to calculate I get a #VALUE! I would like to hide #VALUE! Can anyone point me in the right direction? Thanks Digital2k You might try writing your formulas like =IF(ISERROR(your_formula),"",your_formula) The disadvantage of such an approach it that your_formula will often be calculated twice, resulting in a performance hit. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Digital2k" <digital2k@adelphia.net> wrote in message news:W5OdnenfQ6bvCCfZnZ2...

I have an EXCEL file (named 'Master') with 2 worksheets. On each of these worksheets I have formulae which links with data on 2 different EXCEL files (named 'Data 1' & 'Data 2'). These 2 files only hold tables containing data inputs (i.e. no formulae). When I open the 'Master' file it asks if I want to refresh the Links, which I do. When this is completed one worksheet in the 'Master' file shows what I expect it too, having been linked to 'Data 1' file. However, the other worksheet shows #VALUE! in all the cells that relate to a link in the...

The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

Hi I have a summary table running using this formula: =IF($B45="","",F11+R11+AD11+AP11+BB11+BN11+BZ11) B45-B51 contains linecodes for items such as 12021756, there are then other vlooks running. When B45-b51 are empty there is a 0 in the box which is pulled through from another sheet. The trouble is when a 0 is looked up and the accompanying row is empty of data i.e F11+R11... I get a #value! error which spoils the summary. When B45 contains a 0 i want a 0 to appear in my summary box so the formula will work.. Much Appreciated Brian There is no way you can get a va...

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello, <br> I need to manipulate some date/time strings that I'm importing from an external source. The strings look like this: <br><br>10/30/2009 3:19:08 PM <br><br>I need to separate date & time, and use subtotals etc. on change of date & change of hour. <br><br>I thought the best thing would be to separately extract the date and time, then use DATEVALUE() on the date string so I can format it as yyyy-mm-dd and do calculations. <br><br>...

Hello, I'm adding two columns, first- A7:A125 with the formula in A126 second- E7:E125 with the formula in E126 Then I divided the results for a percentage in a cell at the top E3. First question: Can I do this: =SUM(E7:E125)/(A7:A125) I mean obviously I can't because I get this error/if I can it will save me a bit of work... am I missing something? Second question: cell A126 has a total that I want to use in every calc as I copy it from E3 to F4 etc.... Regards, Kevin try =SUM(E7:E125)/sum(A7:A125) -- Don Guillett SalesAid Software donaldb@281.com "Kevin&qu...

Hi I have a report which picks up data from a pivot table using the GETPIVOTDATA function. e.g. =GETPIVOTDATA("Total",Electricity!$A$3,"Planning Group",$A5,"Sub-Transaction",D$2,"Summary","Energy Price") It works well in Excel 2003. However when I send the file to a colleague who uses Excel 2000 all the results are replaced by "#Value!" He has the analysis toolpack installed, Any ideas what the problem might be? I think I've worked out what the problem is. To save on file size I unchecked the “Save data with table layout”...

Previously I had this formula which was working fine: =IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3=" inch"),VLOOKUP($E$6,lookup!$C$3:$D$19,2,0),IF(AND($E$5=3,$E$2="...3/12...",$E$3=" inch"),VLOOKUP($E$6,lookup!$E$3:$F$19,2,0),"")) But after I add another part onto the end of the formula I am no getting a #VALUE! error (even though, if I enter information in th referenced cells, it seems to work fine and return the correc numbers): =IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3=" inch"),VLOOKUP($E$6,lookup!$C$3:$D$19...

I've tried as per the help to use conditional formatting and that doesn't hide the text. I've tried adding =IFERROR to my formula and it tells me there's an error in the formula where I've added ,"", My formulas are =INT(MID($B5,1,(SEARCH("M",$B5,1))-1)) and =(MID($B5,(SEARCH("M",$B5,1))+1,(SEARCH("C",$B5,1))-1*((SEARCH("M", $B5,1))+1))) I'd rather not use script thanks. You need to get at the source of the error. If the cell does not contain "M" then SEARCH will return an error, so this can be trapped. You...

The following will not solve (or start to solve?): H4=I4+J4+K4 I4=60.73 J4=constant/(constant*M4) L4=(I4+constant)/2 M4=function(L4) I get this error on cells H4, J4, L4, and M4: #VALUE! Sometimes J4 gets a #DIV/0! error. I know this converges, as other programs using these formulae will converge. Does excel assume 0 for inital guess? -this would explain the #DIV/0 error. Is it posible to assign an initial guess for the cells in question? Iteration is enabled with 100 maximum iterations, and 0.1 maximum change. I appreciate any help on this! Peter J4 will return a #DIV/0 error if M...

Got this far, don't know how to negate #Value! error, in equation: (thanks) =IF(TODAY()>=DATE(MID(T9,2,2)+100,MID(T9,4,2),MID(T9,6,2)-$H$7)),"dd", to do with, if not have :yymmdd in left of general text cell T9... or even if the dd portion is not present. You can use a variation of the following formula to change the error result of a formula, but it requires that you write the formula twice, once as a condition, and once as the false result: =IF(ISERROR(Formula_To_Evaluate),ErrorValue,Formula_To_Evaluate) Here is another expample, using the PMT function. =IF(ISERRO...

Ok, I need to find the difference between two dates so I'm using the DATEVALUE function to do this. But I keep getting a damn VALUE error. I've even tried entering the examples that excel gives you but I still get the same thing. Shouldn't it be for example =DATEVALUE("01/31/2003") Thanks for any help I won't comment on the second half of your name, but if you're really Italian, should your date not be "31/1/2003"? Anyway, check your dat format with your Windows settings -- Kind Regards, Niek Otten Microsoft MVP - Excel "ItalianStallion...

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.1.0 080409). When I use Mac Excel 2008 to open excel files that I created in Windows Excel 97 2004, all my pivot tables convert correctly but any REFERENCES to any fields within the pivot table do NOT show the valid values that Excel 97 showed. Instead all refrences have "#VALUE!" in the cell. There is a valid reference startement there but no valid value. How can I fix this? You are a few updates behind: make ...

Why does the following formula give me a #VALUE! error? =IF(AND(OR(E7="E-120-D",E8="E-120-D",E9="E-120-D",E10="E-120-D",E11="E-120-D",E12="E-120-D",E13="E-120-D",E14="E-120-D",E15="E-120-D",E16="E-120-D"),OR("38X12","40X12","42X12","44X12","46X12","48X12","50X12","52X12","54X12","56X12","58X12","60X12","62X12","64X12","66X12","68X12",&qu...

Greetings, I have a problem I can't seem to be able to fix! I have reviewed all possible solutions through the help files with out a resolution. I have attached a copy of the spread sheet I am working with. The problem begins with "C6" where I have an array formula that produces a #VALUE! error. I have identified the problem being on the the row it is suming from sheet "LOG". As soon as I change the Row letter to another Row it works OK. I have attempted several formating changes and have not been able to resolve. Has anyone seen this scenario problem before ...

Function FrstLtrs(MyStr As String) As String Dim temp Dim i As Long TmpStr = Split(Trim(MyStr)) 'MsgBox "String" + TmpStr For i = 0 To UBound(TmpStr) If Not (UCase(TmpStr) = "OF") And Not (UCase(TmpStr) = "FOR") And Not (UCase(TmpStr) = "THE") And _ Not (UCase(TmpStr) = "AND") And Not (UCase(TmpStr) = "A") Then If Asc(Left(TmpStr(i), 1)) >= 65 And _ Asc(Left(TmpStr(i), 1)) <= 90 Then FrstLtrs = FrstLtrs & Left(TmpStr(i), 1) End If End If Next End Function Find the mo...

Here's what I'm trying to do. I have a few rows of cells executing a function that inserts a dollar amount when "Yes" is entered in the adjacent cell. Here's what the cell looks like COLUMN I COLUMN J "YES" =IF(I9="YES",15.99,"") "YES" =IF(I10="YES",15.99,"") "YES" =IF(I11="YES",15.99,"") As you can see I did my best to avoid Excel from printing "FALSE" when the cell is empty The problem is that I'm trying to sum the dollar amounts in COLUMN J using this function...

I have to IF Functions that work independently and I am trying to combine them into one function. My desired end result is either the text "BUY" or "SELL". 1st Function - IF(AND($B$13="High",$I$13="S"),"SELL","") If cell B13 has the text "High" and I13 has the text "S", then generate the word "SELL". If this is false, then leave the cell blank. 2nd Function - IF(AND($C$9="Low",$I$9="L"),"BUY","") If cell C13 has the text "low" and I13 has the text &q...

hi, I have an excel worksheet where in cell c52, I divide C6/C20, which gives me #value!. I know that this is because C6 = 0. I wanted to know if anyone has any tricks where I can somehow use an IF statement to turn the #value! into a 0. any help would be greatly appreciated. cheers. --- Message posted from http://www.ExcelForum.com/ Hi Kenkozo! =C6/C20 won't return #VALUE! if C6 = 0 unless C20 is text. I'd check back at your problem and post back as you should be certain of the cause of errors before you trap them. -- Regards Norman Harker MVP (Excel) Sydney, Australia njh...

in cell E8 I have this formula: =IF(ISBLANK(D8),"",D8*$B$2) I get the #VALUE! error. Can someone tell me why and how I can correct it. I am just trying to say that if D8 is empty don't show anything, if populated multiply D8 by B2. Thank you for your help -- Diana Oh, I forgot to say that D8 does have a formula in it. So that must be why I get the error--the cell is not blank. Is there anything I can do so it does not show on spreadsheet. -- There is probably some text character, like a space in the cell D8 =IF(OR(ISTEXT(D8),ISBLANK(D8)),"",D8*$B$2) V...