If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel 2007 cell reference problems
I have a problem with some accounting spreadsheets for my company. The
finance department are using Excel 2007, with all office updates applied and are experiencing the following problem: When trying to reference cells from other worksheets zero values are returned. E.g. ='BalanceQ1'!A1 The physical cell can contain any number (formatted to number). But if I use the reference above, 0 is returned. The same thing happens when trying to SUM cells from multple sheets. However, this only happens to about 90% of occurances. |
#3
|
|||
|
|||
Excel 2007 cell reference problems
Hi Steven,
I'm afraid that I am unable to post any sample data as the spreadsheet is confidential accounting data. The reference ='BalanceQ1'!A1 references the cell A1 from worksheet BalanceQ1. Apparantly this worked in Excel 2003 but I haven't been able to find any reference to it in 2007. I have already disable add in's and executed Excel in safe mode but this has now affect. Matt ""steven du"" wrote: Dear Matt, Thank you for posting in our Partner Online Technical Community. From your description, I understand that zero values are returned when trying to use the reference "='BalanceQ1'!A1" from other worksheets. If there has been any misunderstanding, please let me know. I am not quite clear about what the reference "='BalanceQ1'!A1" stands for and please help to clarify it. The general troubleshooting steps are listed below to check whether the issue is caused by add-ins: a. Click Start menu, type "Excel /s" (without the quotation marks) in the Run box. b. Press Enter and click Open menu to open the problematic file. c. Please let me know whether the issue can be reproduced or not. In the meantime, is it convenient for you to send a sample Excel file to me at with 43689087 in the subject? I will check it on my test machine. Please also help me to capture somes screenshots to show the symptom in detail: a. Press the Print Screen key (PrtScn) on your keyboard. b. Click the 'Start' menu. c. Type 'mspaint' in the Run box and Press Enter. d. In the Paint program, click the 'Edit' menu, click 'Paste', click the 'File' menu, and click 'Save'. e. The 'Save As' dialogue box will appear. Type a file name in the 'File name:' box, for example: 'screenshot'. f. Make sure 'JPEG (*.JPG;*.JPEG;*.JPE;*.JFIF)' is selected in the 'Save as type' box, click "Desktop" on the left pane and then click 'Save'. Please send this saved JPEG file to my email account as well. Have a nice day. Regards, Steven Du Microsoft Online Support Microsoft Global Technical Support Center ================================================== == When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== == This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | From: =?Utf-8?B?TWF0dHBz?= | Subject: Excel 2007 cell reference problems | Date: Tue, 26 May 2009 03:05:02 -0700 | | I have a problem with some accounting spreadsheets for my company. The | finance department are using Excel 2007, with all office updates applied and | are experiencing the following problem: | | When trying to reference cells from other worksheets zero values are | returned. E.g. | | ='BalanceQ1'!A1 | | The physical cell can contain any number (formatted to number). But if I use | the reference above, 0 is returned. The same thing happens when trying to SUM | cells from multple sheets. However, this only happens to about 90% of | occurances. | |
#4
|
|||
|
|||
Excel 2007 cell reference problems
FWIW, I just tried a very simple example of the same thing in Excel 2007 SP1 and
again in SP2, and it works as you'd expect. If you're on the original pre-SP1 release of Excel, applying the current service packs might be a good idea. I'm afraid that I am unable to post any sample data as the spreadsheet is confidential accounting data. The reference ='BalanceQ1'!A1 references the cell A1 from worksheet BalanceQ1. Apparantly this worked in Excel 2003 but I haven't been able to find any reference to it in 2007. I have already disable add in's and executed Excel in safe mode but this has now affect. Matt ""steven du"" wrote: Dear Matt, Thank you for posting in our Partner Online Technical Community. From your description, I understand that zero values are returned when trying to use the reference "='BalanceQ1'!A1" from other worksheets. If there has been any misunderstanding, please let me know. I am not quite clear about what the reference "='BalanceQ1'!A1" stands for and please help to clarify it. The general troubleshooting steps are listed below to check whether the issue is caused by add-ins: a. Click Start menu, type "Excel /s" (without the quotation marks) in the Run box. b. Press Enter and click Open menu to open the problematic file. c. Please let me know whether the issue can be reproduced or not. In the meantime, is it convenient for you to send a sample Excel file to me at with 43689087 in the subject? I will check it on my test machine. Please also help me to capture somes screenshots to show the symptom in detail: a. Press the Print Screen key (PrtScn) on your keyboard. b. Click the 'Start' menu. c. Type 'mspaint' in the Run box and Press Enter. d. In the Paint program, click the 'Edit' menu, click 'Paste', click the 'File' menu, and click 'Save'. e. The 'Save As' dialogue box will appear. Type a file name in the 'File name:' box, for example: 'screenshot'. f. Make sure 'JPEG (*.JPG;*.JPEG;*.JPE;*.JFIF)' is selected in the 'Save as type' box, click "Desktop" on the left pane and then click 'Save'. Please send this saved JPEG file to my email account as well. Have a nice day. Regards, Steven Du Microsoft Online Support Microsoft Global Technical Support Center ================================================== == When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== == This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | From: =?Utf-8?B?TWF0dHBz?= | Subject: Excel 2007 cell reference problems | Date: Tue, 26 May 2009 03:05:02 -0700 | | I have a problem with some accounting spreadsheets for my company. The | finance department are using Excel 2007, with all office updates applied and | are experiencing the following problem: | | When trying to reference cells from other worksheets zero values are | returned. E.g. | | ='BalanceQ1'!A1 | | The physical cell can contain any number (formatted to number). But if I use | the reference above, 0 is returned. The same thing happens when trying to SUM | cells from multple sheets. However, this only happens to about 90% of | occurances. | |
#5
|
|||
|
|||
Excel 2007 cell reference problems
Thanks for Steve's suggestions. Matt, please help to check whether the Office 2007 Service Pack 2 has been installed or not. In the meantime, I have made a test in my Excel 2007 SP1 and used the reference "='Sheet name'!A1". The zero value is displayed properly and the issue cannot be reproduced on my test machine. Based on the current situation, please create a new Excel file via Excel 2007 and then use the reference ='BalanceQ1'!A1 to check whether the same issue occurs or not. Please also help me to capture somes screenshots to show the symptom in detail: a. Press the Print Screen key (PrtScn) on your keyboard. b. Click the 'Start' menu. c. Type 'mspaint' in the Run box and Press Enter. d. In the Paint program, click the 'Edit' menu, click 'Paste', click the 'File' menu, and click 'Save'. e. The 'Save As' dialogue box will appear. Type a file name in the 'File name:' box, for example: 'screenshot'. f. Make sure 'JPEG (*.JPG;*.JPEG;*.JPE;*.JFIF)' is selected in the 'Save as type' box, click "Desktop" on the left pane and then click 'Save'. Please send this saved JPEG file to me at with 43689087 in the subject. Have a nice day. Regards, Steven Du Microsoft Online Support Microsoft Global Technical Support Center ================================================== == When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== == This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | From: =?Utf-8?B?TWF0dHBz?= | Subject: Excel 2007 cell reference problems | Date: Wed, 27 May 2009 00:31:01 -0700 | | Hi Steven, | | I'm afraid that I am unable to post any sample data as the spreadsheet is | confidential accounting data. | The reference ='BalanceQ1'!A1 references the cell A1 from worksheet | BalanceQ1. Apparantly this worked in Excel 2003 but I haven't been able to | find any reference to it in 2007. | | I have already disable add in's and executed Excel in safe mode but this has | now affect. | | Matt | | ""steven du"" wrote: | | | Dear Matt, | | Thank you for posting in our Partner Online Technical Community. | | From your description, I understand that zero values are returned when | trying to use the reference "='BalanceQ1'!A1" from other worksheets. If | there has been any misunderstanding, please let me know. | | I am not quite clear about what the reference "='BalanceQ1'!A1" stands for | and please help to clarify it. The general troubleshooting steps are listed | below to check whether the issue is caused by add-ins: | | a. Click Start menu, type "Excel /s" (without the quotation marks) in the | Run box. | b. Press Enter and click Open menu to open the problematic file. | c. Please let me know whether the issue can be reproduced or not. | | In the meantime, is it convenient for you to send a sample Excel file to me | at with 43689087 in the subject? I will check it on | my test machine. | | Please also help me to capture somes screenshots to show the symptom in | detail: | | a. Press the Print Screen key (PrtScn) on your keyboard. | b. Click the 'Start' menu. | c. Type 'mspaint' in the Run box and Press Enter. | d. In the Paint program, click the 'Edit' menu, click 'Paste', click the | 'File' menu, and click 'Save'. | e. The 'Save As' dialogue box will appear. Type a file name in the 'File | name:' box, for example: 'screenshot'. | f. Make sure 'JPEG (*.JPG;*.JPEG;*.JPE;*.JFIF)' is selected in the 'Save as | type' box, click "Desktop" on the left pane and then click 'Save'. | | Please send this saved JPEG file to my email account as well. | | Have a nice day. | | Regards, | | Steven Du | | Microsoft Online Support | Microsoft Global Technical Support Center | | ================================================== == | When responding to posts, please "Reply to Group" via your newsreader so | that others may learn and benefit from your issue. | ================================================== == | This posting is provided "AS IS" with no warranties, and confers no rights. | | | -------------------- | | | From: =?Utf-8?B?TWF0dHBz?= | | Subject: Excel 2007 cell reference problems | | Date: Tue, 26 May 2009 03:05:02 -0700 | | | | | I have a problem with some accounting spreadsheets for my company. The | | finance department are using Excel 2007, with all office updates applied | and | | are experiencing the following problem: | | | | When trying to reference cells from other worksheets zero values are | | returned. E.g. | | | | ='BalanceQ1'!A1 | | | | The physical cell can contain any number (formatted to number). But if I | use | | the reference above, 0 is returned. The same thing happens when trying to | SUM | | cells from multple sheets. However, this only happens to about 90% of | | occurances. | | | | | |
Thread Tools | |
Display Modes | |
|
|