Inter sheet formula won't format properly

Hi
MS Excel 2003.

I am writing a formula with a reference to a cell in another sheet, and 
Excel is behaving oddly. Sometimes it will give me the result, ie what 
is in the target cell, but sometimes it gives me the formula.

The formula I am writing is simply

='2'!A22

thereby trying to get cell A22 from the sheet called 2.

Why is Excel not always just giving me what's in the target cell? 
Sometimes all I can see is ='2'!A22. It's driving me nuts.

I have looked at all the formatting options with no luck; I have toggled 
(using Ctrl`) the formula view with the normal view, but no luck.

Any ideas?
Cheers
Alec
0
alecmcq (4)
2/14/2008 7:18:57 AM
excel 39879 articles. 2 followers. Follow

6 Replies
538 Views

Similar Articles

[PageSpeed] 48

Sound like the cell with the formula in is formatted as text.
Brian

0
bc5562 (13)
2/14/2008 8:08:20 AM
On Feb 14, 8:08 am, b...@brianjwc.vispa.com wrote:
> Sound like the cell with the formula in is formatted as text.
> Brian

Right click your cell, change the number type to 'General' in the
Number tab of the Format Cells dialog.  Once you've done this try
typing in the formula again and it should work just fine.

Hope this helps,
Matt Richardson
http://teachr.blogspot.com
0
2/14/2008 8:56:46 AM
Matt Richardson wrote:
> On Feb 14, 8:08 am, b...@brianjwc.vispa.com wrote:
>> Sound like the cell with the formula in is formatted as text.
>> Brian
> 
> Right click your cell, change the number type to 'General' in the
> Number tab of the Format Cells dialog.  Once you've done this try
> typing in the formula again and it should work just fine.
> 
> Hope this helps,
> Matt Richardson
> http://teachr.blogspot.com

OK, thanks, this worked, BUT it seems that Excel is automatically 
changing the format of a cell back to text when I edit it. Why? Each 
time I edit the formula in the cell, it changed the format from General 
to text, so each time I have to change it back. I don't get it.
Cheers
Alec
0
alecmcq (4)
2/14/2008 8:44:33 PM
Saved from a previous post.

Excel likes to help.

Try this on a test worksheet.
Select A1 and hit ctrl-; (to put the date in the cell)
now select B1 and type:  =a1

Notice that excel changed the format of B1 to match the format in A1.

Now format D1 as Text.
put ASDF in D1
put =D1 in E1
You see ASDF.

With E1 selected, hit the F2 key and then enter (to pretend that you're changing
the formula).

Excel has "helped" you by changing that cell's format to text.

I don't know of any way of changing this behavior.

I just select the cell, and reformat it to General (or whatever I wanted).  I
hit F2 and then enter (to reenter that formula).

Sometimes this feature is nice, sometimes it ain't.

Alec wrote:
> 
> Matt Richardson wrote:
> > On Feb 14, 8:08 am, b...@brianjwc.vispa.com wrote:
> >> Sound like the cell with the formula in is formatted as text.
> >> Brian
> >
> > Right click your cell, change the number type to 'General' in the
> > Number tab of the Format Cells dialog.  Once you've done this try
> > typing in the formula again and it should work just fine.
> >
> > Hope this helps,
> > Matt Richardson
> > http://teachr.blogspot.com
> 
> OK, thanks, this worked, BUT it seems that Excel is automatically
> changing the format of a cell back to text when I edit it. Why? Each
> time I edit the formula in the cell, it changed the format from General
> to text, so each time I have to change it back. I don't get it.
> Cheers
> Alec

-- 

Dave Peterson
0
petersod (12005)
2/14/2008 9:45:09 PM
Dave Peterson wrote:
> Saved from a previous post.
> 
> Excel likes to help.
> 
> Try this on a test worksheet.
> Select A1 and hit ctrl-; (to put the date in the cell)
> now select B1 and type:  =a1
> 
> Notice that excel changed the format of B1 to match the format in A1.
> 
> Now format D1 as Text.
> put ASDF in D1
> put =D1 in E1
> You see ASDF.
> 
> With E1 selected, hit the F2 key and then enter (to pretend that you're changing
> the formula).
> 
> Excel has "helped" you by changing that cell's format to text.
> 
> I don't know of any way of changing this behavior.
> 
> I just select the cell, and reformat it to General (or whatever I wanted).  I
> hit F2 and then enter (to reenter that formula).
> 
> Sometimes this feature is nice, sometimes it ain't.
> 
> Alec wrote:
>> Matt Richardson wrote:
>>> On Feb 14, 8:08 am, b...@brianjwc.vispa.com wrote:
>>>> Sound like the cell with the formula in is formatted as text.
>>>> Brian
>>> Right click your cell, change the number type to 'General' in the
>>> Number tab of the Format Cells dialog.  Once you've done this try
>>> typing in the formula again and it should work just fine.
>>>
>>> Hope this helps,
>>> Matt Richardson
>>> http://teachr.blogspot.com
>> OK, thanks, this worked, BUT it seems that Excel is automatically
>> changing the format of a cell back to text when I edit it. Why? Each
>> time I edit the formula in the cell, it changed the format from General
>> to text, so each time I have to change it back. I don't get it.
>> Cheers
>> Alec
> 

Definitely not a help in what I am doing. PITA.

Anybody know how to turn this off?
Alec
0
alecmcq (4)
2/15/2008 2:44:56 AM
> I don't know of any way of changing this behavior.

Alec wrote:
> 
> Dave Peterson wrote:
> > Saved from a previous post.
> >
> > Excel likes to help.
> >
> > Try this on a test worksheet.
> > Select A1 and hit ctrl-; (to put the date in the cell)
> > now select B1 and type:  =a1
> >
> > Notice that excel changed the format of B1 to match the format in A1.
> >
> > Now format D1 as Text.
> > put ASDF in D1
> > put =D1 in E1
> > You see ASDF.
> >
> > With E1 selected, hit the F2 key and then enter (to pretend that you're changing
> > the formula).
> >
> > Excel has "helped" you by changing that cell's format to text.
> >
> > I don't know of any way of changing this behavior.
> >
> > I just select the cell, and reformat it to General (or whatever I wanted).  I
> > hit F2 and then enter (to reenter that formula).
> >
> > Sometimes this feature is nice, sometimes it ain't.
> >
> > Alec wrote:
> >> Matt Richardson wrote:
> >>> On Feb 14, 8:08 am, b...@brianjwc.vispa.com wrote:
> >>>> Sound like the cell with the formula in is formatted as text.
> >>>> Brian
> >>> Right click your cell, change the number type to 'General' in the
> >>> Number tab of the Format Cells dialog.  Once you've done this try
> >>> typing in the formula again and it should work just fine.
> >>>
> >>> Hope this helps,
> >>> Matt Richardson
> >>> http://teachr.blogspot.com
> >> OK, thanks, this worked, BUT it seems that Excel is automatically
> >> changing the format of a cell back to text when I edit it. Why? Each
> >> time I edit the formula in the cell, it changed the format from General
> >> to text, so each time I have to change it back. I don't get it.
> >> Cheers
> >> Alec
> >
> 
> Definitely not a help in what I am doing. PITA.
> 
> Anybody know how to turn this off?
> Alec

-- 

Dave Peterson
0
petersod (12005)
2/15/2008 2:53:09 AM
Reply:

Similar Artilces:

regression lines and r-square in work sheets instead of charts
1. I am mathematically challenged 2. I have a series of numbers such as sales per month for a 60 month period. From these numbers I have to predict the next month, quarter’s sales. I have to (1) enter the historic data, (2) prepare a line graph, (3) add a trend line by choosing one of the regression types (4) set the period and ask for the r-square value, (5)"zoom in" on the graph to find the value. (6) go back to the worksheet and plug in the predicted number and the r-square value and (7) repeat steps 2-6 for each of the remaining 5 regression models. 3. I could enter the ...

custom number format #13
Hi, Is there a way (besides formatting as text), to have numbers show as in the following example? i.e., : 25 40.01 40.10 etc I can't seem to get the decimal point to disappear when I enter a number without one. TIA Jim Hi Jim, You could use a User-Defined Function to apply the formatting. The following does that for the range A1:A100: Private Sub Worksheet_Change(ByVal Target As Range) With ActiveSheet If Intersect(Target, .Range("A1:A100")) Is Nothing Then End With Target If Int(.Value) = .Value Then .NumberFormat = 0 If Int(.Value) <> .Value Then...

Travel sheet in Excel 2003
I work with the State, and we have to report mileage using an Excel 2003 template - provided, of course, by 'The State' . The format is starting destination - end point - purpose - mileage (end of row). Next row is last end point - new destination - purpose - mileage (end) etc. Here's what I am searching for. Is there an easy keyboard (or other) shortcut to fill in 'last destination' on the new row? The closest I can find is control + ' but this just fills in the what is in the cell above. I need a trick to fill in the information from the cell in the abo...

Cell formatting #8
This is a multipart message in MIME format. --=_alternative 0051FBC080256E20_= Content-Type: text/plain; charset="us-ascii" Hello. I am using Excel 2000 with Windows 2000. I have a spreadsheet that will not update any shading. The cells are not locked and the spreadsheet is not protected. When I try to change cell colour, the screen shows the change but a print preview or print hardcopy does not. I have copied the worksheet into another blank worksheet and the formatting works fine. Although I have a workaround I am curious as to why this is happening. Can anyone shed any light ...

Format docs from existing Files
Is there an easy way to use an existing document file to format a current file? I have some plain text files that I would like to match an existing document file. TIA Jim You can use the Organizer to copy styles from one document to another. To get to the Organizer, press Alt+F8 to open the Macros dialog and click the Organizer button. Once the styles have been copied to the plain text document, you need to apply the styles to the appropriate parts of the text. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; pl...

Conditional formatting: How to set condition "formula" with is "date" formatted
I want to use Conditional Formatting. I opt for "formula". The condition should be that a cell F9 that is formatted to Date 14-Mar-07 (see cell format) need to be filled with a date (any date). If empty then FALSE. What is the formulah I need to fill in. DATE(F9<>0) failed for me. This is the condition that is set for cell H9. Thank you. Bart Excel 2003 =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0) -- --- HTH Bob (there's no email, no snail mail, but somewhere sho...

Importing invoices
Hi, I was wondering what formats (comma seperated values?) Great Plains can read to import basic invoice information (customer, invoice#, date, subtotal, tax, total, etc) for accounts payable and accounts receivable. I have a client interested in converting from his current document format into something that Great Plains can import for his customers that use Great Plains, but I have never programmed for Great Plains. If there is some standard file format that I can use, that would be perfect. Any information or a point in the right direction would be appreciated. --Mike The best to...

Contains command in a Formula
Hello and thank you so much in advance for helping me - I am working o a complex project that requires me to identify certain text within string of variables and label the cell as "Home Stereo", Electronics etc. A2 is http://w.126x.com/?kw=home%20electronics&src=excel&ref=help A3 is http://w.126x.com/?kw=home%stereo&src=excel&ref=help In B2 I would like to enter a formula that says if in A2 the word "home" and "electronics" appear then B2 would say "Home Electronics". And I would like in the same formula to assign different labels so...

unprotect sheet greyed out
I have been using a worksheet for awhile that I have to unprotect everytime I want to update. TOday the unprotect sheet option is greyed out and I get a message saying the worksheet is protected. Any ideas Office 2000 Thanks!! Is the workbook shared? If it is, turn off sharing (Tools>Share Workbook) Then unprotect the worksheet Yadira wrote: > I have been using a worksheet for awhile that I have to > unprotect everytime I want to update. > TOday the unprotect sheet option is greyed out and I get a > message saying the worksheet is protected. > Any ideas Office 2000...

Excel: Copy to a new tab but keep formula references from old shee
I have a roughly 25 rows of formulas that i need to copy to a new tab that reference numbers within the same worksheet. How do i get these newly copied formulas within the new tab to still reference all of the information in the original tab? On Apr 24, 5:10=A0pm, Cheese man <Cheese m...@discussions.microsoft.com> wrote: > I have a roughly 25 rows of formulas that i need to copy to a new tab tha= t > reference numbers within the same worksheet. > > How do i get =A0these newly copied formulas within the new tab to still > reference all of the information in...

Copy a formula pattern
I am trying to copy a formula that pertains to another sheet in the same workbook. How can I do this? =IF(I1="","",'Team Winners'!$AB7) =IF(I2="","",'Team Winners'!$AB7) =IF(I3="","",'Team Winners'!$AB7) =IF(I4="","",'Team Winners'!$AB8) =IF(I5="","",'Team Winners'!$AB8) =IF(I6="","",'Team Winners'!$AB8) Every third row I need to increase the row by one. If I just do a copy every row goes up by one. I need to do this ...

Excel Formula Help #3
I have data consisting of New Traffic and Returning Traffic. I need to find out the percentage of New Traffic that is Returning Traffic. What formula do I use? Help!!! Please tell us a bit more about how the data is set out. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "cempire5" <cempire5@discussions.microsoft.com> wrote in message news:3E141B74-B168-4AD2-8BAC-DEB46E7DD5E0@microsoft.com... >I have data consisting of New Traffic and Returning Traffic. I need to >find > out the percentage of New Traff...

Deleting data entries but not formats
Hi newsgroup, I have some trouble with excel... and with a document which I use for serial mails via Mail Merge. Now I need to clean all old data entries and prepare it for the next use. But if I simply delete the entries or do it via Clear-Entries then I have problems because conditional formatting will also be deleted and furthermore the mail merge cant work anymore because some fields are not found anymore. Does someone know how I can only delete the data entries, nothing else? The Mail merge runs via SQL and I'm not quite good in it... Thanks a lot in advance and a nice weeken...

Change absolute cell reference in formula
I've got a bunch of cells set up to be the sum of 2 specific cells. The formula I'm using is ($G$216+$G$3617) for example. I now need to duplicate this in column I so my new formula should be ($I$216+$I$3617) but when I cut and paste from G to I, the formula is still referencing G, which is to be expected. Is there a quick way to update the formulas so they reference I instead? Thanks. Mark Find/replace -- Jim "Mark Christensen" <markc@ssbrakes.com> wrote in message news:OeSCRjX9HHA.484@TK2MSFTNGP06.phx.gbl... | I've got a bunch of cells set up to be th...

Case format for getting cell value from a worksheet.
Good Afternoon, I need to create a series of Case conditions based on the value of a cell on a certain worksheet in my open workbook..For the life of me I cannot get the syntax right and I'd appreciate any help i could get. My condition is on a worksheet named "Lookup_Values" Cell "M3" is a value used as a case condition. what I want to do is retrive that value and use it to compare against each case like below. RPT_Date = ("Lookup_Values!M3") Select Case True Case RPT_Date = "January" Perform my actrions Exit Sub ...

formula query #3
I have a group of 52 "weekly" spreadsheets , each one is named after this week's start date in a prcise format e.g. 130305.xls. I want to create a cell within each weekly sheet which simply contains this weeks date. Obviously I could manually enter this data but I would prefer it if Excel could automatically access the filename of this spreadsheet and embed it accordingly in the cell. Can anyone tell me if this is possible? TIA Wayne Chip Pearson has formulas for returning the Sheet Name or the File Name: http://www.cpearson.com/excel/excelF.htm#FileName Wayne K...

Formatting Pivot Table- when doble clicking to show Detail
He there, Can I modified the format of the detail that I get once I doble click my pivot Table? Or Is there a way that once I dobleclick in my pivot table to obtain the detail, a macro can be fired to apply the format that I want?. ...

not sure if I'm using the correct formula for the result I want?
I'm trying to calculate a 'low net' score. A persons 'actual score' minus 'their handicap' = 'low net' score. I have this worksheet setup like this; A4:A23 = list of names Columns B-U have the weekly calculated low net scores B4:U23 = the calculated 'low net' scores using this formula =SUM(Scorecard!B4-Scorecard!Y4); (this is for player one) Scorecard!B4 is player one's first week 'actual score', Scorecard!Y4 is player one's calculated 'handicap'. In Row B25:U25 I have this formula {=MIN(IF(B$4:B$23>0,B$4:B$23))}, t...

help, formula to read results and paste to other document
have document labeled for each week, within this there are 6 sheets, mon/tues/wed/thurs/fri/total i want the total for each person to be put into a other excel document, is this possible ?? if so how ? A "document" is a workbook/file? "each week" means how many weeks? 52 for a year? Each workbook has 6 worksheets? "each person" is situated where within the above configuation? On one of the 6 sheets within the 52? workbooks? On all of the 6 sheets in each workbook? Gord Dibben MS Excel MVP On 20 Aug 2006 14:20:49 -0700, paulaskew@hotmail.com wrote: ...

Word docs copied into Outlook have strange formats when received by other email browsers
When I send mail from Microsoft Outlook, it often looks strange when received by another user in Eudora. Recently, strange three line breaks were inserted between one paragraph and another, which only showed up when the mail was opened in Eudora. There were other odd formatting errors too. Yet the mail looked fine when received by another user in Outlook. Perhaps the problem was that the Outlook email originated in a copied-in Word document? Documents originated as PDF files do not seem to cause the same problems. Does anyone know the best way to copy documents from Word into Outlook, so a...

Personal folders was not closed properly
Hi, I get the message below periodically and was wondering if anyone else does and what the solution is. I have a 1.2 GB pst file on my local drive. "The file Personal Folers was not closed properly. The file is being checked for problems. Some questions; -What version of Outlook? -Are you running any add-ins? -Is the outlook.exe process not running in Task Manager when you close Outlook -ran scanpst.exe already against it? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Create an Office XP CD slipstreamed with Service Pack 3 ----...

Mixing Number and Text Formats
Morning all, I have a cell that concatenates several others, the complete cell reads something like this: BUY 200000 LMT 23.67 The text will change so I can't hard code that, but what I would like is a comma separator (200,000) for the 200000 (this number will also change). After spending far too much time on this, I have a feeling that you can't mix a text and number custom format. Prove me wrong. Cheers, J. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.co...

Outlook 2000: E-mail Format
When I open an E-mail form by clicking on New Mail Message and then click the "To" box, the E-mail address that I get is the recipient's name. Does anyone know how to set the default to show the actual E-mail address rather than the name? This is important for those people who have more than one E-mail address. Thanks. Spike drajr@houston.rr.com it uses the email display name, which you can change for every contact. -- "Spike" <dudman@verizonmail.com> wrote in message news:O7GZtxvaDHA.1280@tk2msftngp13.phx.gbl... > When I open an E-mail form by clickin...

tabbed view like excel sheet
how create views like excel sheet in is botton like tabed view This was in MSJ few years ago: http://www.microsoft.com/msj/0599/c/c0599.aspx --------- Ajay Kalra ajaykalra@yahoo.com > how create views like excel sheet in is botton like tabed view http://www.codeproject.com/tabctrl/foldertabcontrol.asp Lynn ...

Writing formatted XML
How would I write an XmlDocument to a file so that each node is properly indented and followed by a carriage return? Thanks...Dan http://msmvps.com/coad/archive/2004/06/22/8692.aspx Using XmlDocument.Save(string file) produces a file with nicely indented elements. XmlDocument.OuterXml returns a string without any formatting. If you want a nicely formatted string (to display to the user, write to console, etc), without directly writing to a file, you must use the XmlTextWriter. using System.IO; using System.Xml; public static string FormatXML(XmlDocument doc) { // Create a stream bu...