Offset works for numbers, not for text

I have several cells containing calls to offset of the form:

	=OFFSET(Col1,Index,0)
	=OFFSET(Col2,Index,0)
	=OFFSET(Col3,Index,0)

All but one of the columns have numeric data. They all work fine. One
column has text data. The source cell is formatted as "text" as is the
destination (where the formula above is). Instead of showing the text
data, it shows the formula. I've ftried formatting the destination
cell as Text, General, & Number. They all show the formula.

Help!
0
Prof
9/8/2009 7:52:29 PM
excel 39879 articles. 2 followers. Follow

7 Replies
476 Views

Similar Articles

[PageSpeed] 38

Just re-formatting is not enough. Re-format AND re-enter the formula (F2, 
ENTER)

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Prof Wonmug" <wonmug@e.mcc> wrote in message 
news:oocda5190k07pe7gj0as8a4u3luevrhkuo@4ax.com...
>I have several cells containing calls to offset of the form:
>
> =OFFSET(Col1,Index,0)
> =OFFSET(Col2,Index,0)
> =OFFSET(Col3,Index,0)
>
> All but one of the columns have numeric data. They all work fine. One
> column has text data. The source cell is formatted as "text" as is the
> destination (where the formula above is). Instead of showing the text
> data, it shows the formula. I've ftried formatting the destination
> cell as Text, General, & Number. They all show the formula.
>
> Help! 

0
nicolaus (2022)
9/8/2009 8:44:07 PM
Format the cell with the formula as General.
The with that cell still selected, hit F2, then hit enter.

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.

Prof Wonmug wrote:
> 
> I have several cells containing calls to offset of the form:
> 
>         =OFFSET(Col1,Index,0)
>         =OFFSET(Col2,Index,0)
>         =OFFSET(Col3,Index,0)
> 
> All but one of the columns have numeric data. They all work fine. One
> column has text data. The source cell is formatted as "text" as is the
> destination (where the formula above is). Instead of showing the text
> data, it shows the formula. I've ftried formatting the destination
> cell as Text, General, & Number. They all show the formula.
> 
> Help!

-- 

Dave Peterson
0
petersod (12004)
9/8/2009 8:46:31 PM
On Tue, 8 Sep 2009 22:44:07 +0200, "Niek Otten" <nicolaus@xs4all.nl>
wrote:

>Just re-formatting is not enough. Re-format AND re-enter the formula (F2, 
>ENTER)

That worked, thanks. But.....why?  ;-)
0
Prof
9/8/2009 9:57:23 PM
On Tue, 08 Sep 2009 15:46:31 -0500, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>Format the cell with the formula as General.
>The with that cell still selected, hit F2, then hit enter.

It appears that the formatting doesn't matter as long as it's not
Text. Just the F2 + Enter got it to work. Changing the formatting to
General, Number, or several others (not Text), got the same result.

This is a very geeky program. It has exceeded even my geekiness.
0
Prof
9/8/2009 10:04:32 PM
In your earlier post, you said that you already changed the format to General. 
So excel was just waiting for you to "change" the cell.

Prof Wonmug wrote:
> 
> On Tue, 08 Sep 2009 15:46:31 -0500, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
> 
> >Format the cell with the formula as General.
> >The with that cell still selected, hit F2, then hit enter.
> 
> It appears that the formatting doesn't matter as long as it's not
> Text. Just the F2 + Enter got it to work. Changing the formatting to
> General, Number, or several others (not Text), got the same result.
> 
> This is a very geeky program. It has exceeded even my geekiness.

-- 

Dave Peterson
0
petersod (12004)
9/8/2009 11:13:35 PM
On Tue, 08 Sep 2009 18:13:35 -0500, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>In your earlier post, you said that you already changed the format to General. 
>So excel was just waiting for you to "change" the cell.

But why does it wait on just this one cell? I have 5-6 such cells, but
only the one that comes from a source cell that is formatted as text
behaves this way. If I change the formatting for any of the others,
the cell display changes immediately.

Geeky, I say, very geeky.


>Prof Wonmug wrote:
>> 
>> On Tue, 08 Sep 2009 15:46:31 -0500, Dave Peterson
>> <petersod@verizonXSPAM.net> wrote:
>> 
>> >Format the cell with the formula as General.
>> >The with that cell still selected, hit F2, then hit enter.
>> 
>> It appears that the formatting doesn't matter as long as it's not
>> Text. Just the F2 + Enter got it to work. Changing the formatting to
>> General, Number, or several others (not Text), got the same result.
>> 
>> This is a very geeky program. It has exceeded even my geekiness.
0
Prof
9/8/2009 11:42:35 PM
I have no idea why it does this.  But I know that it does.

If you format the cell as general and your formula points at a cell formatted as
text, then the first time you enter the formula, the cell will be treated as a
General format cell containing a formula.

But if you change the formula (or just hit F2 followed by enter), then excel
will look at that sending cell and notice that it's Text and change the format.

And like I described in my earlier post--sometimes it's useful (dates) and
sometimes, it's not.

Prof Wonmug wrote:
> 
> On Tue, 08 Sep 2009 18:13:35 -0500, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
> 
> >In your earlier post, you said that you already changed the format to General.
> >So excel was just waiting for you to "change" the cell.
> 
> But why does it wait on just this one cell? I have 5-6 such cells, but
> only the one that comes from a source cell that is formatted as text
> behaves this way. If I change the formatting for any of the others,
> the cell display changes immediately.
> 
> Geeky, I say, very geeky.
> 
> >Prof Wonmug wrote:
> >>
> >> On Tue, 08 Sep 2009 15:46:31 -0500, Dave Peterson
> >> <petersod@verizonXSPAM.net> wrote:
> >>
> >> >Format the cell with the formula as General.
> >> >The with that cell still selected, hit F2, then hit enter.
> >>
> >> It appears that the formatting doesn't matter as long as it's not
> >> Text. Just the F2 + Enter got it to work. Changing the formatting to
> >> General, Number, or several others (not Text), got the same result.
> >>
> >> This is a very geeky program. It has exceeded even my geekiness.

-- 

Dave Peterson
0
petersod (12004)
9/9/2009 12:44:59 AM
Reply:

Similar Artilces:

SUMPRODUCT not working?
Hi I need to sum a cell range (F2:F90) based on two conditions of two other cell ranges (D2:D90="XXX" and I2:I90=""), where the parameter of the first condition may change ("XXX" changes to "MFB", "TGDolfa", "GATV", etc.) and the second remains constant (I2:I90=""). On one cell I tried the formula: =SUMPRODUCT((D2:D90="MFB")*(I2:I90="")*(F2:F90)) and it worked as I got an accurate result. Now the part that is driving me crazy... When I copied the formula to the cell below and changed the first...

does this work?
http://driverwhiz.com/ Sounds too good to be true. Anyone tried it? Is it free? -- regards, |\ /| | \/ |@rk \../ \/os Oh no. You don't need any drivers if your computer is running fine. When somebody offers you something for free then you should ask yourself "why is this person helping me?" and "what is the catch?" There is no such things as "free lunch" and these driver scanners are all over the place these days partly because Windows is becoming more secure and so hackers are looking for new ways to breach your securit...

how to increase maximum number of columns in excel 2003
Excel 2003 is set to 256 columns, perhaps because my spreadsheet was originally started in Excel 97. How can I increase the maximum number of columns to what is possible in Excel 2003? Quattro Pro has far more columns available. I can't believe that Microsoft would limit Excel 2003 to 256 columns. ????? Believe it. In article <FCBC8D1E-AB0E-4305-9B06-ABD42D1A9B70@microsoft.com>, "ldettering@cogeco.ca" <ldettering@cogeco.ca@discussions.microsoft.com> wrote: > Excel 2003 is set to 256 columns, perhaps because my spreadsheet was > originally started in E...

Out of Office Assistant Auto Reply not working
The out of office assistant does not send a note to anyone outside of my network. Using Outlook 2000 with Exchange Server. Auto Reply is sent to anyone that is local on my network but does not send outside. Any suggestions? Automatic replies to the internet are disabled by default in Exchange 5.5 and later. Check wth your Exchange administrator to see if he/she is willing to change this. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:0a6f01c3d3b4$300761b...

working with html stationery
Through Outlook 2002 I am using Word to compile my e- mails. I wish to place a background 'clear day' in my e- mails. I have tried changing it in Outlook, but to no avail. Any suggestions please. ...

? MVPs' Word FAQs website fails to work with Safari, but MVPs' Entourage site works fine
Can't the MVPs' webpages for Word FAQs be modified to make them compatible with the the Safari browser? There is NO problem in using Safari to access the MVPs' webpages for Entourage FAQs. I've used both Safari 1.0 and, since upgrading to OS 10.3.1, Safari 1.0 with the same success with the MVPs Entourage FAQs webpages and the same failures with the MVPs Word FAQs webpages. That said, I wish to commend the MVPs for creating the FAQs resources for both Entourage and Word users. That's a really valuable, generous service you've provided. Respectfully, Norman Nager ...

Column numbers/letters
Excel used to have lettered columns but this new version I'm using has numbered columns. Does anyone know how I can get my lettered columns back? "Adam Turner" <anonymous@discussions.microsoft.com> wrote in message news:D3A7906C-6D37-436B-BDD2-87BA91325BD7@microsoft.com... > Excel used to have lettered columns but this new version I'm using has numbered columns. Does anyone know how I can get my lettered columns back? Tools > Options > General tab Uncheck R1C1 reference style. This is not a change between versions but a setting that has always been there. ...

Tab in numbering does not move to next "level" in the list hierarchy
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel This is how things used to go: <br><br>I start my list by typing &quot;1.&quot; and hitting the space bar. When I hit return, the next line begins with &quot;2.&quot; However, it used to be that when I pressed the tab key, the &quot;2.&quot; went to the next level: &quot;a.&quot; (followed by &quot;i.&quot; if I hit tab again to get to the third level). <br><br>Yesterday this stopped working. When I hit tab after my &quot;2.&quot; it simply ta...

vlookup and offset
Hi, Can anyone PLEASE help me with the following? - it's driving me cRaZy!! I want to look up a value in column B and return the contents of the cell across 4 and down 4 from the value looked up in column B. Is this possible? Rachel ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message news:RachelS.vyc4y@excelforum-nospam.com... > Hi, > > Can anyone PLEASE help me with the foll...

XSL Transform
I'm learning how to use the XSL transform functionality and can't get it to work. In a book I'm reading on it, it says that I can do like I did below and just add a value like <root_node/> in there and I should be able to transform any XML source document I have so that the results would simply contain <root_node/>. I wanted to try that, just so I could see if I understand how this is working and wrote the code and XSL file below. However, my value for szTransformedXML is always an empty string. Does anyone see what I'm doing wrong? Here's a method I wrote ...

how to get the lenth and offset of paragraph?
To All: I have a pragram ,here only is a example similar to My pragram �� these are some data : AAAA this is a test! here is the content of AAAA. BBBB this is a test,I want to know that between AAAA and BBBB lenth . CCCC this is a test! here how can I know CCCC excursion is what relative to AAAA�� best regards! Terrcy Application : SDI View : Derived from CEditView. Menu item added : Calculate Length Paste paragraph mentioned in your mail in view.. be sure to remove any leading blank spaces before 4 BBBB line... Imp...

xcel Number Formatting Problem
Hi there, I have exported accounting information from a company data website int excel. For some reason, where profit figures had a 1000 seperator (, on the website, when exported into excel the 1000 seperator became decimal point (.). Also, the profit figures taken from the website ar in GDP th i.e. excluding the last '000'. Therefore, please can you help me do the following: 1) Replace the unwanted decimal point (.) back to a 1000 seperato (,). 2) Add 000 to the end of the profit figures so that they are in writte in full. Many thanks And -- andyp16 ------------------------...

Getting the number of processors
What's the best way to get the number of processors? I was thinking of just getting it from the environment variable. -- Thanks. "Saucer Man" <saucerman@nospam.net> wrote in message news:uLnKelwiKHA.1572@TK2MSFTNGP04.phx.gbl... > What's the best way to get the number of processors? I was thinking of > just getting it from the environment variable. If WMI is an option then this may be a start: Sub main() MsgBox "Count processors=" & CountProcessors & vbCrLf & _ "Query processors=" & QueryProcessor...

Transation sequence number issue
Happy new year everybody!! I have a customer with this situation. his invoice must be sequentially he can't skip a transaction number for his invoices, when a transaction is aborted a number is skiped. i konw that one is not hard to fix but this is the main issue when he do a return he need to have a differnt count numbers than the transactions number for example when he sale something the transation number 1545 is generated but is he return or do a store credit the transaction number is 145 without affectin the secuence os the sale transaction number. can be done? the other probl...

OMA not working #4
Hi, I'm trying to get OMA to work. It works when I browse from a pc to http:...\oma but when I try to browse from my smartphone I got the error: Error Code: 500 Internal Server Error: The data passed to a system call is too small (122) I installed the root certificate of the ssl provider on the smartphone. Thanks in advance Be sure that DNS is properly setup and that the Cert has the FQDN or it won't work. Be sure DNS Host is setup. Also, make sure it's passing SSL (the Web Access Site) sometimes you have to enable it. j- "ExchangeServer" <exchangeServe...

Cash draw not working
I have RMS ver. 1.3 refresh installed with a serial epson tm u220 printer which is connected to a Dynapos cash draw. The printer is connected to the PC via a 20 ft serial cable and it uses a windows driver. The printer and cash draw have been configured but only the printer works, the draw does not pop open when a receipt is tendered. With a parallel printer connected to the pc both the printer and cash draw work. Can anyone tell me why i can't get the draw to function with the serial printer? Thanks did you check the 'pop cash drawer' check box for each tender type you...

Change text in textbox in chart in Excel 2007
After I changed from Excell 2003 to Excel 2007, the following VBA code, which changed the date in a textbox in a chart, no longer worked: Sheets("All Styles").Select ActiveChart.Shapes("Text Box 1026").Select Selection.Characters.Text = Range("Date").Value The macro recorder doesn't record anything when I try to use it to get some code. Any ideas for fixing this? Excel 2007 Shape Text Sub Textbox() Dim d As Date With Sheets("Summary") d = .Range("DDate") With .ChartObjects(1).Chart.Shapes(1).TextEffect ...

Reducing the number of lines of code required
I'm trying to create a form for work and I have the following lines o code created within it... I have another 20 checkboxes which wil require similar lines of code. Is there any way to cut down on th total number of lines required (as it will end up being a ridiculou size otherwise)? CheckBox5.Value = False TextBox14.Enabled = False TextBox14.BackColor = RGB(128, 128, 128) CheckBox6.Value = False TextBox15.Enabled = False TextBox15.BackColor = RGB(128, 128, 128) CheckBox7.Value = False TextBox16.Enabled = False TextBox16.BackColor = RGB(128, 128, 128) CheckBox8.Value = False TextBox17.E...

Vlookup and offset
Hi, I would like to lookup a users ID in a table and check if they have a paticular qualification, a normal VLOOKUP will do that but then I would like to refer to a date in the header to see when they gained the qualification so the cell can determine whether they are qualified based on the current date. I could do this without checking the date but that would disguise the lack of skills for the rest of the past records as soon as the skill was recorded. I think I need to combine an IF, VLOOKUP and an OFFSET but am at a loss as to how to put it all together. Hope someone can help. ...

Outlook 2003 - Pictures received as text
I just changed over to Outlook 2003 from Windows Mail. Now when certain people send me pictures inline, they come to me as pages and pages text, like this: Content-Type: image/jpeg; name=snow Content-Transfer-Encoding: base64 Content-Description: snow 2-23-10 1.jpg Content-Disposition: attachment; filename="snow 2-23-10 1.jpg" /9j/4Sr2RXhpZgAASUkqAAgAAAAKAA8BAgAWAAAAhgAAABABAgAmAAAAnAAAABIBAwABAAAAAQAA ABoBBQABAAAAwgAAABsBBQABAAAAygAAACgBAwABAAAAAgAAADEBAgAQAAAA0gAAADIBAgAUAAAA 4gAAABMCAwABAAAAAQAAAGmHBAABAAAA9gAAAB4HAABFQVNUTUFOIEtPREFLIENPTVBBTlkAS09E QUsgRUFTWVNIQ...

Memory failure : Getting Sum to work
I added the number 200 3 times (vertical) . Got the correct answer but i can't seem to get the answer to appear in the total cell. Just the formula shows up . What am i doing wrong ? Format the cell with the formula as General (or number--just not text). Then with that cell selected, hit F2 followed by enter. Drew Cutter wrote: > > I added the number 200 3 times (vertical) . Got the correct answer but i > can't seem to get the answer to appear in the total cell. Just the > formula shows up . What am i doing wrong ? -- Dave Peterson It sounds as though the cell...

is RMS work with Vista?
This is a multi-part message in MIME format. ------=_NextPart_000_0014_01C8059F.4583E5B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Does anyone know that RMS 2.0 will work with windows Vista? Thanks ------=_NextPart_000_0014_01C8059F.4583E5B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"...

Page Number
Hi, In Excel 2000, is there a formula to make a cell contain page number and total number of pages? We want to have a heading with our logotype and other information and as Excel 2000 can not include pictures in heading we want it on top of every sheet. I can make Excel print rows on top, but can I include page number and total number of pages in the sheet? Thanks in advance Leif I'd use an actual header (or footer). File|Page Setup|Header/Footer Tab Click on Custom header (or footer) You see a couple of icons that look like a sheet of paper. the icon with # in it is Page#. The ...

Working with DOM in VC++
Hello, I think I have a problem that can't be solved this way. But I'll try anyways. I am working with SVG and JavaScript and I have created a VC++ dialog that uses CWebBrowser. CWebBrowser's function is calling web pages that use JavaScript and SVG. My JavaScript is using DOM to alter SVG content but it is only done on DOM level and whatever change the user is making doesn't affect the actual file contents. What I need to do is to change the contents of a web file itself. For example. If I my original SVG code is: <text id="some_id"> Original ...

Landscape pages, Page Numbers, and running headers
In order to display page numbers for landscape pages, I use a frame in the header, stretching down the side (which will be the top) of the page. This works fine except sometimes--it removes the header from the next section. That is, I have portrait pages with their odd/even headers--Section Break--landscape page with odd/even frame headers--Section Break--no header on portrait page! The footer is there, but I simply cannot access the header. WHY, WHY? And when I kludge the page numbers into the portrait page header area, with yet another frame, it shows up on the landscape pages...