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
854 Views

Similar Articles

[PageSpeed] 54

"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:

Validation list in a conditional formula
I would like to include a validation list (drop down choices) in a conditional formula in Excel. Something along the lines of =IF(A4="RAIN",$B$11,IF(A4="TEMPERATURE",$D$11,"")) where $B$11 has a drop down choice of appropriate units for rain based on a list from a column of cells (e.g. containing inches, cm, mm) and $D$11 has a drop down choice of appropriate units for temperature based on a different list from a different column of cells (e.g.containing Kelvin,Centigrade,Fahrenheit). But I can't get the validation list with the drop down choices to b...

suggested ways to manipulate XML files in .Net?
Hi everyone, I'm going to manipulate some XML files in my next project-- crawl through the tags, compare if file X has the same nodes as file Y, and if not, what the differences are and who's missing what. I'll also be comparing values between nodes in the two files, for when both documents do have the same named nodes. What .Net classes should I be looking at to accomplish these tasks? Thanks a bunch. Steve Hershoff wrote: > I'm going to manipulate some XML files in my next project-- crawl through > the tags, compare if file X has the same nodes as file Y, and...

Strange behavior when copying formula.
Hi, Here is what I'm trying to do. I want to show the summation of the first two rows' number in the third row. I use the summation function to get the first result. Then, I copy and paste the result to the other two, but they show the same results as the first one. I check the formula of all three results, and they are all correct and referring to the right cells. Why is that? 1 2 3 1 2 3 2 2 2 Leo Do you have Tools/Options/Calculation set to Manual? If so, click the Automatic button. In article <eE#EeXFhDHA.2512@TK2MSFTNGP09.phx.gbl>, "Leo Leon...

Save n Refresh New Form
Hi All, I have use the 'Save' command button in the access2000. However, I need additional function when I click the 'Save' button. Click 'Save' - 'Yes' --> Save the data and then refresh into new form with the Log Number(Primary Key) increase to the next number No issue on the 'No' function. Pls help. Thank you. Private Sub SaveData_Click() On Error GoTo Err_SaveData_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 Exit_SaveData_Click: Exit Sub Err_SaveData_Click: MsgBo...

Excel 2007 and Word 2007 Hangs when using the File -> Open command
I have a couple users that when they have Excel 2007 or Word 2007 open, and click the File -> Open option, and choose a file, it either takes about 3 minutes to open the file or the program will crash. If they were to double click on the documents or spreadsheets, the doc or sheet will open right up. This is happening on the user's Hard drive as well as network drives. Once you get a file opened in Word or Excel, however, the next time you go to File -> Open, it's really fast to open the file. ...

Save or Export to CSV
Hello, I have a macro that i recorded that is intended to export (save) data from the active worksheet within a workbook to a CSV file. The problem i'm having is that the macro is saving the entire workbook to a CSV. My file name is even changing. Following is the code: ---------------------------------------------------------------- Sub SaveAsCSV() ' ' SaveAsCSV Macro ' Application.DisplayAlerts = False ActiveSheet.SaveAs Filename:= _ "Z:\Timesheet\Time Sheet 2009.csv" _ , FileFormat:=xlCSV, CreateBackup:=False Applic...

Saving chart as an image file
I want to create a pie chart in Microsoft Excel, then save it as an image file (.jpg or .gif) so I can put it on a website. How do I do this? Secondly, as I create my pie chart, how do I set my data labels? I want to change them from the default (numbers) to my own description for each slice. Jon Peltier has some instructions in his Excel Charting FAQ article for exporting a chart in gif format: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon008 To add your own labels to the chart, you can use Rob Bovey's XY Chart Labeler. It's a free add-in that yo...

Integration Manager--log file name?
Is there any way to know the name of the log file (we're writing our logs to text file) from within one of the "After" integration events? If we're successful, we need to send the user an email telling them that the event is successful. We're currently cycling through the whole folder to find the latest one, but if there were a variable in the Integration Manager VBScripts that I could use, that would be much better. Thanks. PSC: If I recall correctly, IM has a Document Error and Integration Error event in the integration properties. Rather than looking at the...

File Sharing between XP Pro and XP Home
I have a few questions concerning file sharing between XP Pro and XP Home in a Workgroup environment. I would really appreciate any help. 1)From what I'm gathering on the internet (I've read multiple articles) you cannot even connect a Windows XP Home computer to a domain. I'm I interpreting this correctly? 2)I have a new server with XP Pro and 5 other computers with XP Home. I only have 3 computers (which includes the new XP Pro server) that really need access to everything (or the main folders) on the server. The other 3 computers need limited access. If I purchase X...

Public Folders: "Document not saved"
Infrastructure Background: Exchange 2000 running on Windows Server 2000 Desktop: Windows Professional 2000 with Outlook 2000 client. Problem: People are receiving the following error message whilst trying to save Office documents into a Public Folder: "The disc is full. Free some space on this drive or save document on another disc. Try one of the following: Close any unneeded windows or save the document on another disc" Investigation reveals: 1) The is over 80GB of free disk space on the server the Public Folder data is stored on 2) The people concerned have suffient permiss...

Conditional formula 05-12-10
trying to construct a formula for the following for the same cell: if a2 > 6, then e2 = 0 if a2 = 6, then e2 = 1 if a2 = 5, then e2 = 2 if a2 = 4, then e2 = 3 if a2 = 3, then e2 =5 this is for a golf scoring system. a2 values are hole scores and results are "handicap" point scores. Any help greatly appreciated Try this: =3DIF(A2>6,0,IF(A2<=3D3,5,7-A2)) Hope this helps. Pete On May 12, 4:14=A0pm, desmond1412 <desmond1...@discussions.microsoft.com> wrote: > trying to construct a formula for the following for the same cell: > if a2 > ...

Multiple If True Values?
Does anyone know of a way to check for a value in two different cells, and if a certain combination of values exists, change the format of another cell? Any help would be much appreciated. Thank You. Start by experimenting with Format|Conditional Formatting Then come back if more help is needed best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lee" <Lee@discussions.microsoft.com> wrote in message news:07DC594F-6045-4A81-B8B9-19F295A2AAA5@microsoft.com... > Does anyone know of a way to check for a value in two different cells, and &g...

PDF file #5
I have Publisher 2002 and would like to insert a .pdf file into a document I am working on. How do / Can I do this? Many thanks, Ger ...

Stored Procedure with array of values
I have the query below in a stored procedure in MS SQL 2005 server. My problem is that I would like to be able to pass in multiple questionID values. So I would like to replace A.QuestionID = @QuestionNumber with a method where I can pass in a list of question numbers. For example instead of passing in 286 I would like to pass in 286, 289, 412, and 513. The number of values passed in would vary from 1 to 15 or 20. I imagine the line A.QuestionID = @QuestionNumber would be edited to something along the lines of A.QuestionID IN ( @QuestionNumber ) SELECT Coun...

Finding the cell loacation that matches a specific value in a rang
Hi, I have a list of references in column A going from A3:A240. I need a formula that can find a specific reference within that range and give me back the location of the reference. Example: A1 7569 A2 1245 A3 3256 A4 7895 If I input 3256 it needs to return either A3 or 3. Thanks, ZUO This maybe... =MATCH(3256,A1:A100,0) Although I would probably use a cell reference in place of the hard-coded 3256 value. -- Rick (MVP - Excel) "Zuo" <Zuo@discussions.microsoft.com> wrote in message news:F6225750-C572-44A1-A914-083DFF38A1FC@...

linked files
When I open a file it says it is linked to another file. When I check the links it is linked to a file I no longer have. How do I find where the link is coming from and get rid of it. Going to the Edit Links doesn't seem to get rid of the file. Charlie C Try Bill Manville's excellent "FindLink" utility .. It's available at Stephen Bullen's page: http://www.oaltd.co.uk/MVP/Def�ault.htm -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Charlie 5280" <Charlie 5280@discussions.microsoft.com>...

Retrieve .pst files
My PC at work is upgraded from windows NT to Windows 2000. Before upgrade I backed up all the outlook 2000 .pst files. Now I have outlook 2003. I unable to retreive .pst files. viji.pst file is available under C:\Documents and Settings\jviji\Local settings\Applicationdata/microsoft/outlook. From outlook menu file -> data file management ->add -> outlook 97-2002 personal folder.pst -> viji.pst prompts to enter passwd. Please reply how do I retrieve the passwd protected .pst file. This are very important emails. Please reply. Thanks Viji Viji Jayaraman <jviji@ems.att.co...

Use with Office Communicator saving Conversations (IM)
Our office just upgraded our email, and now we have Office Communicatior available. OC allows the saving of conversations to Outlook. I noticed that when a conversation is closed or saved and returned to after a time delay, there are 2 conversations in the folder. Is there a way to only have 1 conversation in the folder? or to have the seperate conversations be distinct, i.e. convo 1 at 930am and ends at 1015 is one, and another covo starts at 215-225. now i'll have a conversation saved at 1015 that has all im from 930, as well as a convo at 225 that has the 215-225 as we...

How can I access a file in Outlook Public Folder by VBscript
It does not have to be VBscript.. I need sync the file with my local harddrive... Thanks. Nix wrote: > It does not have to be VBscript.. > I need sync the file with my local harddrive... > > Thanks. Why does it need to be a script at all? You can add your public folder to your PF\Favorites (drag it there) and include it in your send/receive settings for offline use with your OST file. ...

pulling cell "value"
in the cell C4 i have the formula =now() and I have that cell formatted to custom mmmm so that it displays as January. I am now trying to write some VBA code to look at cell C4 and take the January and then do a vlookup on the array that i have named months. months is the cells D14:E25 column D has a list of the months and column E has a list of numbers that corresponds to these months. This is a custom list so I can not use the standard numbers that excel uses for the months. The vlookup in vba should take the January and find the number in col E that corresponds to it th...

how to define scroll bar's page size value?
Hi all, I have a confusion about calculating scroll bar's page size. In my application, I am drawing an image according to scroll bar position. m_iGeneratedImgWidth - Width size of the image (this value is always bigger) rect.right - Width of the drawing area. how to define scroll bar's page size value? SCROLLINFO info; info.cbSize = sizeof(SCROLLINFO); info.fMask = SIF_RANGE|SIF_PAGE|SIF_POS; info.nPos = 0; info.nMin = 0; info.nMax = m_iGeneratedImgWidth - rect.right; info.nPage = ? In my OnHScrollBar() function, for SB_PAGELEFT / SB_PAG...

Insatantiate Generic Collection with Initial Values?
You know how you can instantiate an array of, say, strings and specify intial values at the same time with something like: string[] myStrings = new string[3] { "value1", "value2", "value3" }; Can the same type of thing be done for a List<string> object? I've done a lot of web searching but I can't find any sample code that illustrates this. Joe Cool wrote: > You know how you can instantiate an array of, say, strings and specify > intial values at the same time with something like: > > string[] myStrings = new string[3] {...

moving files
How do I move files from publisher 98 to publisher 2000? The programs are installed separately. If you are talking about the documents you created, you wouldn't have to. But if you wanted to - copy and paste. -- JoAnn Paules MVP Microsoft [Publisher] "dunefarm" <dune@discus.micro.com> wrote in message news:3854FB0D-CB6B-4D3A-A922-0A982890D3FD@microsoft.com... > How do I move files from publisher 98 to publisher 2000? The programs are > installed separately. ...

open excel 2007 refresh query from batch file
Hello all, I have a simple script saved as "C:\refresh_excel.vbs" that opens an Excel file, and does a refresh for a query on Sheet 1. I created a scheduled task to run this every day. It worked fine for previous versions of Excel, but since I have upgraded to Excel 2007 it doesn't work. Any help it helping me figure this out would be appreciated. /*contents of refresh_excel.vbs*/ Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkBook = objExcel.Workbooks.Open("C:\reports\UPS.xls") objWorkbook.Sheets("...

Arrears Help Files
Hello all, I don't seem to have the help file for the Arrears transactions. I can't find it on my system don't see a reference to it in the main GP 10 help file. We have GP 10 SP 4. I looked in KB see no reference to that particular problem anywhere. TIA Anyone? On Feb 10, 8:12=A0am, Brian <nixtoo...@hotmail.com> wrote: > Hello all, I don't seem to have the help file for the Arrears > transactions. =A0I can't find it on my system don't see a reference to it > in the main GP 10 help file. =A0We have GP 10 SP 4. =A0I looked in KB see= no...