Excel2007 Replace formulas with {=#Value!} when saving file

The file is in Excel 2003 (created in Excell 2003) format. "FileName 
[Compatibility Mode] ..." displayed on title bar when loaded in Excell 2007.

There are several worksheets in the workbook with array formulas referring 
to other worksheet. When saving the file a message pops up saying "saving 
this file in 2003 format will lose information, formulas will be converted 
to {=#Value!} as the 2003 format cannot handle the quatity of formulas in 
this workbook". If you select "Continue" that is exactly what happens: poof, 
most of your array formulas are replaced.

Well Excel 2003 handles them perfectly. It is Excel 2007 in Compatibility 
Mode that cannot.

Bottom line: if you have Excel 2003 files with a lot of array formula, you 
cannot use Excel 2007 and hope to keep these files working (for your 
coworkers who still use a pre-Excel 2007 version of Excel !!! 

0
9/28/2007 7:06:00 PM
excel 39879 articles. 2 followers. Follow

2 Replies
576 Views

Similar Articles

[PageSpeed] 25

"Arthur" <Art...@OregonKoiGardens.com> wrote...
>The file is in Excel 2003 (created in Excell 2003) format.
>"FileName [Compatibility Mode] ..." displayed on title bar when
>loaded in Excell 2007.
....
> . . . When saving the file a message pops up saying "saving
>this file in 2003 format will lose information, formulas will be
>converted to {=#Value!} as the 2003 format cannot handle the
>quatity of formulas in this workbook". . . .
....

Care to show us a few examples of what these formulas should look
like? And how many formulas are there?

0
hrlngrv (1990)
9/28/2007 8:01:51 PM
There are literally thousands of array formulas in this workbook. Excel 2007 
[compatibility mode] keeps a few thousands formulas to what there should be 
then convert the rest of them to {=#Value!}.

Excel 2003 had no problem with this workbook.

Excel 2007 handles it properly after converting the workbook to the new 2007 
format.

"Harlan Grove" <hrlngrv@aol.com> wrote in message 
news:1191009711.181647.308210@d55g2000hsg.googlegroups.com...
> "Arthur" <Art...@OregonKoiGardens.com> wrote...
>>The file is in Excel 2003 (created in Excell 2003) format.
>>"FileName [Compatibility Mode] ..." displayed on title bar when
>>loaded in Excell 2007.
> ...
>> . . . When saving the file a message pops up saying "saving
>>this file in 2003 format will lose information, formulas will be
>>converted to {=#Value!} as the 2003 format cannot handle the
>>quatity of formulas in this workbook". . . .
> ...
>
> Care to show us a few examples of what these formulas should look
> like? And how many formulas are there?
> 

0
10/2/2007 1:14:35 AM
Reply:

Similar Artilces:

windows7 excel2007 problems
I cannot open more than 1 worksheet at a time. Aero shake and snap not working with Excel. Thank you. ...

In Excel: If I know a file has 147,000 records, but Excel will on.
In Excel: If I know a file has 147,000 records, but Excel will only load 65,000, how do I get to the rest of the file? MVP Chip Pearson's website has some code that may help you out: http://www.cpearson.com/excel/imptext.htm HTH Jason Atlanta, GA >-----Original Message----- >In Excel: If I know a file has 147,000 records, but Excel will only load >65,000, how do I get to the rest of the file? >. > > attached a VBS script that I wrote to do that. Was unable to save it, Jim .. Could you paste it in the message itself ? Thanks -- Rgds Max xl 97 --- GMT+8, 1� 22...

Change to formula
I am using this formula to display the current date on a sheet, I require it to show the actual day before, what do I need to change. =IF(AND(B6="",B4<>""),NOW(),IF(AND(B4<>"",B6<>""),B6,"")) =IF(AND(B6="",B4<>""),TODAY()-1,IF(AND(B4<>"",B6<>""),B6,"")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "bbc1" <bbc1@discussions.microsoft.com> wrote in message news:9F920C16-159A-478D-AEC3-81BD6ABC5...

financial excel formula
Trying to use excel to determine the % return on a stock from the tim you purchase until you sell, adjusted for time. ie., key in Pv o money today; input Fv or (the selling price); enter the amount of tim expressed in months or years (N); calc the rate of return %? p $50.00 FV $74.00 N 3 years % return =??? using a texas instruments ba-35 calculator th answer is 13.96%. Can't get this out of excel, any suggestions -- Message posted from http://www.ExcelForum.com =RATE(3;0;-50;74) The financial function follow the rule that incoming and outgoing money are different in sign. Hence...

Topic: Oh! NOOO! APOSTROPHE FOUND RE: Replace and Removing SQL VBA HELP!!!
Dear All,All nigth long and still do not know how to fix this query,This works fine until finds an apostrophe and I guess any otherstrange characterRun-time error '3075':Syntax error (missing operator) in query expression "C:\DVD APPZX\Appz Hp\hp drivers\hp\Drivers-Audio\Realteck AC'97 AudioDriver Update(English)' WHERE Tempinfo_Tbl.ID = 10165 ANDTempInfo_Tbl.DirFolder Like 'DIR*"The code replace in one step all the records from my TblTempInfo_Tbl:Actual Record: DIR Dups V.1.0\bas\2006\<Drive>:\Path\Folder\Folder....After Replace: C:\Dups V.1.0\bas\2006If ...

MAX formula
I've entered the following formula to find the largest value in a list: MAX(C3:C3000) So far, there are values in C3 to C35 - the formula is allowing for expansion. The only result I can get is zero. Any suggestions? Jonathan -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17048 View this thread: http://www.excelforum.com/showthread.php?threadid=466699 Are the cells C3 to C35 'numeric' values, ie, does the number show o the right side or left side ...

How and where can I learn advanced formulas?
I would like to learn how to use advanced formulas such as converting combining numbers with text, scientific calculations etc. Is thi something you just have to pick up from sites like this? Are ther books available -- Message posted from http://www.ExcelForum.com I would encourage the use of the function wizard in excel. Gwenerally there is a function or combination of functions to solve what you want to do in Excel. I find it particularly useful. >-----Original Message----- >I would like to learn how to use advanced formulas such as converting, >combining numbers with text, ...

How can I replace a value??
in a column the following formula Sum(R6:R7) Sum (R7:R8) Sum (R8:R9) goes to Sum(R19:R20) I need to replace the first R value with R6 all the way down. Is there an easy way to do this, other than changing each formula individually? Any help apprecited Skip In the top cell: =SUM(R$6:R7) fill down -- Kind regards, Niek Otten Microsoft MVP - Excel "Skip" <ssummer@earthlink.net> wrote in message news:1174333858.941579.297590@l77g2000hsb.googlegroups.com... | in a column the following formula | Sum(R6:R7) | Sum (R7:R8) | Sum (R8:R9) | | goes to | Sum(R19:R20) | | I nee...

return fields associated with max value record
I have a query that returns the max date value correctly, but I need it to return the other data associated with that record. I would like to do it all in one query. tblTransferData houses the ID and AsgStartDate and is joined to tblTransferSnapShot by ID. tblTransferSnapShot holds the Snaphot Dates that I'm returning the max value. I need the other data associated with this max Snapshot Date record, such as Band and Title. Below is the query so far. Thanks SELECT tblTransferData.ID, Max(tblTransferSnapShot.[Snapshot Date]) AS [MaxOfSnapshot Date] FROM tblTransfe...

Changing Source excel file causes links in word to fail
I've got a Word document with about 600 links to an excel spreadsheet. Updating the links works fine until the source excel file is changed thenword changes some portion of the links from Unformatted Text to a spreadsheet cell. Changing the links manually or with a macro there are some observed changes in the links. The cells that change are changed from Automatic to Manual and this happens to all of the cells on a single Excel row. There's nothing wrong with the data and the spreadsheet formats are identical. When I go back to the original settings I can change the ...

Payables transaction file and integration manager
2007 Sep 16 I want to use integration manager to import data from another application to enter payables invoice transaction data. Information will be required for: PM10100 PM Distribution Work File PM10500 PM Tax Work File PM10000 PM Transaction Work File Please let me know if this is correct. Thanks, Jacqueline Chay ...

Sorting Possible avoiding cell Formulas?
Just wanted to know if it is Possible to sort omitting any formul activity? I know, sounds crazy and doesn't make much since, but when I attempt t sort, the data resorts back to the original position based on som formulas I am using. So I guess the main question would be, Is it possible to freeze formul activity to allow general sorting? If Not, I can surely understand Wh ...Thanks In Advance.. -- Mh ----------------------------------------------------------------------- Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3598 View this thread: http://www...

Messages Not Saved to Drafts Folder (but to INBOX folder)
The "solution" (XCLN: Messages Not Saved to Drafts Folder" (MS Knowledge Base 247813)) merely states that MS Outlook's default is to save messages every 3 minutes to the Drafts folder! As the subject heading of this MS KB 247813 states, however, the problem is that messages ARE NOT being saved to the Drafts folder! The "solution" to my reading seemingly ignores this and offers no "way around" or correction of the problem. This sort of response in MS "Help" is so frustrating. It seems so patently obvious that the "solution" ...

Help with formula #20
I have a simple formula for some friends that will input the correct sates tax on two of 10 line items in a list. So it reads =(C20+C24)*.07. OK. Now the line above the tax is a sum of the list, then the tax, and after is the Total with tax. Lets say the subtotal line is C30, tax line is C31 and Total line is C32. I made this into a template because it is opened every day. When you open it you get the #Value in both the Tax and the Total cell because nothing has been entered into the cells in the formula. Is there some way to allow this tax and Toal cells to show $0 rather than the #val...

Replace default CRM form with custom form
I have developed a custom form to represent a custom CRM entity. I would like to load that form when the user double-clicks a record from the lookup list in CRM. I'm pretty sure I do this either in the isv.config file or sitemap.xml file. I've setup both to load this form from either a button or from the Navbar, but neither of these will attach the proper GUID for the record the user wants to open. So can you trigger a custom form to load in place of the default one that CRM creates? Thanks for your assistance. Paul ...

Cannot open files.
I am having trouble opening files after saving them. I keep getting the message "cannot be opened because there are problems with the contents. Details says "Unspecified error" followed with the location in the "document.xml" file. ...

How to generate a string with values from a range?
I ahve a little macro that is passed a range. It does some work on the individual values. I have written a subroutine that writes debug data to a side file. I would like to dump the values in the input range to the side file as a delimited string. If it was an array variable, I could use the Join function. I have tried various combinations, but nothing works. Can someone show me a single line of code that will generate a single string from a range variable? I know I can do it with a loop, but I'd prefer a single statement (perhaps using Join) is possible. Single line of exis...

when dragging formula how do i avoid #DIV/0!
When dragging a formula and a field has a nil value is there a way to avoid the result showing as #DIV/0!. Can it just skip this or return a 0 value or leave blank. Dividing by zero is undefined in math hence the error. Use the If function to test for a zero divisor: Suppose you have a value in Column A and another value in Column B and in Column C you have the formula A5/B5. If B5 is empty - has a value of zero - or has some formula that results in a zero, then your formula in Column C will result in #DIV/0! Solve it by replacing the formula in Column C with this: =IF(B5=0,"",Ad/B...

Replacement for ISA
Hi guys and girls When selling SBS2003, ISA was an awesome product. We love the ability to block AD users / AD Groups or individual PCs from certain services, websites, etc. Since 2008 doesn't include ISA, we are looking for alternatives. We are looking at using ISA 2006, and the effect on costs, etc, vs using a hardware device or other software. Previously on SBS 2003 with ISA, we would use a cheap consumer grade modem. What do you use? brand, model, why and cost? The main requirements are: Ability to allow / deny services based on AD Username, AD Group, and IP ...

Find and Replace #5
I get a square symbol when ODBC'ing Returns in text/memo fields. What character is it that I can use in the Find box in Excel to replace it with another character? Check out this google search thread. http://snipurl.com/5wq6 Gord Dibben Excel MVP On Fri, 23 Apr 2004 04:44:12 -0700, "cwillson@selwayjoyce.co.uk" <anonymous@discussions.microsoft.com> wrote: >I get a square symbol when ODBC'ing Returns in text/memo >fields. What character is it that I can use in the Find >box in Excel to replace it with another character? Many thanks - that's great! ...

include rounding in a formula
i have the following: A B C 1 327.2 3033.56 0.107860072 column C1 is simply A1/B1 . Is there a way that i can include in my formula to round the results to 0.11? What I have been doing is creating another column and entering the formula: round(c1,2) then copying and pasting values I am hoping to eliminate these steps and somehow include the rounding function in the formula. Is this possible? On Wed, 24 Mar 2010 15:38:01 -0700, KrispyData <KrispyData@discussions.microsoft.com> wrote: >i have the following: > > A B C >1 327.2 3033.56 0.1078600...

Changing Date Values!!! (dd/mm/yy) =(
Hi everyone.. im new to this forum :rolleyes: i joined because i wanted a solution to something thats been bugging me ever since i got Excel :eek: I am running Micrososft Excel 2003 in case you wanted to know... The problem is this: When i type a date in the format: (xx/yy/zz) - Excel recognises the xx as the month (ie. mm/dd/yy) When in fact i want it to recognise xx as the day (ie. dd/mm/yy) That means every time i type (1/2/05) into a1 and (2/2/05) into a2 i drag the rest and they come out like this: 1/2/2005 2/2/2005 3/2/2005 4/2/2005 5/2/2005 6/2/2005 7/2/2005 8/2/2005 9/2/...

Using values in drop down boxes in formulae
Is there any way to use the value shown in a drop down box in a sum formula. I am trying to work out the total of a column including whatever number is shown in the drop down box. The formul at the moment is =SUM(C16:C28) with the drop down box positioned on cell C28. Is there any way of linking the drop down box to the cell so the number shown is added as well? Thx --------- www.coffeecozy.com Use your Bodum and give up cold coffee for good! Hi what drop down boy are you suing ('Data - validation')? -- Regards Frank Kabel Frankfurt, Germany "44203 - ExcelForums.com" <...

Find/Replace on Range?
When I highlight a column or some cells I can not restrict a FINE and REPLACE to those highlighted cells. Can this be done so that it doesn't search the whole sheet? Using Excel 2003. All When you select a range of cells and specify "within" Sheet from Options the Find will take place only in the selected range. If you specify "within" Workbook the Find will ignore your selected range and do the workbook. Same goes for Replace. Gord Dibben Excel MVP On Tue, 13 Apr 2004 11:52:54 -0700, "Al Franz" <albert@nospam.netmation.com> wrote: >When I ...

dynamic values formula
I don't even know if the Title is accurate to reflect what I need to ask... Assume cell A1 contains the value 1, and cell A2 contains the value 2. Assume cell B1 contains the value "a" and cell B2 contains the value "b" Let's say I want to put a formula in cell C1 that says it is equal to the value in column B, and the value contained in cell A2, thus returning a value of "b" in cell C1. Would the formula in C1 look something like =C[a2] ? Thanks -- mwc0914 ------------------------------------------------------------------------ mwc0914's Pr...