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

Similar Articles

[PageSpeed] 33

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:

Excel
Text pasted into a cell from Word or other software will keep the formats including carriage returns, line numbers, tabs, etc. Can text be typed into a cell with control characters that will force this type of formating? -- mwaller If I understood your question correctly - you might try hitting Alt+Enter in order to go to a new line in the same cell Micky "mwaller" wrote: > Text pasted into a cell from Word or other software will keep the formats > including carriage returns, line numbers, tabs, etc. > Can text be typed into a cell with control charac...

Updating subforms not working
main form has a dropdown combot box selecting query values which updates a record on main form. Subform being dispaly references this value to display records from a second table. But when I select a value the subform doesn't change. but then I noticed that the sub form is one value behind, not always. select employee but emolpyee detail records don't update. select another employee and last employee records gets displayed. private sub cbEmployee_Change() docmd.Save Me.EmployeeDetails.Requery end sub butthis doesn't seem to work. Try replacing the Change...

Out of Office Assistant Doesn't Work
Hello, I have one user who put their Out of Office assistant on, but it is not replying to any incoming messages. She has no rules setup in her Outlook. The config is Exchange 2003 and she has WinXP with Oulook 2003. Thanks, George In news:s5lig2lbgkkppca0ktp0i2b5nbi1l5msct@4ax.com, George <George@Geo.com> typed: > Hello, > > I have one user who put their Out of Office assistant on, but it is > not replying to any incoming messages. She has no rules setup in her > Outlook. The config is Exchange 2003 and she has WinXP with Oulook > 2003. > > Thanks, > Geor...

Automaic Folder Numbering
This is a line of code that i use to create folders on my computer with what ever value B3 is, now what i would like to do is by clicking a button have it look under a certain folder and audomaticlly enter the next 5 digit number and create a folder with that number and put it into a cell in my worksheet like this - 10001, 10002, 10003, etc...... MkDir "C:\\Globe Photo Engraving\Jobs\" & Range("B3").Value David, I have assumed the sheet name is Sheet1 - enter the starting number in cell B3 there, and run this macro: Sub CreateFolder() Dim MyFilePat...

Telephone number for Telefonica Windows Live Call
Hi. Having trouble adding minutes to my account. Anyone have a phone number or e-mail for either Telefonica or Windows Live Call that I can contact for help? Thanks! Greetings, Supposedly, if you head over to: http://support.live.us.telefonica.com/?Country=US&Language=en Choose Payment and Balance Problems on the side, and then choose one of the "problems" listed, then choose " If you are still having trouble after the above steps, please Click Here ", you'll reach a form to assist. -- Jonathan Kay Microsoft MVP - Windows Live Messenger MSN Mes...

Reset recently used files in Publisher to higher number
I want to set the number of recently used files in Publisher to nine from the four that it shows me at present. I can't find anywhere to change this. I'm using Publisher 2003. I don't think you can do that in Publisher but I'm sure that someone will chirp up if I'm mistaken. -- JoAnn Paules MVP Microsoft [Publisher] "Ken" <Ken@discussions.microsoft.com> wrote in message news:07E5ABDF-9C44-4091-8620-1AA38931EF91@microsoft.com... >I want to set the number of recently used files in Publisher to nine from >the > four that it shows me at prese...

Help: my reminders don't work
Help: I'm running OL 2003 on WinXP pro connected to an exchange server. I run this configuration on both my laptop and my desktop. On my desktop the calendar reminder pop ups and alert sounds don't work but they do on my laptop. As far as I can tell, the configuration is identical. I've tried the /cleanreminders and /resetfolder switches, but the don't make any difference. Any advice appreciated. Thanks. -- Dab Cut off: yourhead to respond Have you checked your Sounds and Multimedia options on the desktop to ensure that your sounds are working correctly and th...

Problem Working with Named Ranges
Hi, On one worksheet in my workbook, I have an outline with expandable and collapsable rows (using the group function). In another worksheet, I have different buttons which runs different macros that will link me to the sections of the outline that I want expanded (while the rest remains collapsed). I was able to get a macro to work using absolute values for a cell position, but I have been unsuccesful trying to work with name ranges. This is a problem, as I frequently add new rows to the outline so I'm constantly having to update the macro. Can someone help me put relative v...

how do work with validetion
I know that its possable to do with validation that I should not be abl to enter in column f:f that the total should be more then 10 -- shlom ----------------------------------------------------------------------- shlomo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1530 View this thread: http://www.excelforum.com/showthread.php?threadid=32061 Shlomo, In the Validation dialog, choose "Custom" from the Allow list, and use the formula =SUM(F:F)<=100 -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson...

Outlook 2000 forms does not work in Exchange 2000
I have a Outlook 2000 leave form that user used to apply leave. The form is then emailed to the approval and the approval opened the form to approve or reject. The reply is then emailed back to applicant. The Outlook form did not work for the approval. When you click on the approve button nothing happened. I understand MAPI doesn't work on Exchange 2000. Here is the approve subroutine: Sub cmdApproveLeave_Click() Set cControls = Item.GetInspector.ModifiedFormPages ("Message").Controls If cControls("txbDisableFunctions").text = "888" Then Msg = ...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

Serial Number
I have a table with fields: DT, INVOICE, AMT Now invoice numbers are different unique keys but not numeric, how to add a fourth temporary column in a query to insert serial numbers as well like: Serial, DT, INVOICE, AMT 1 2 3 4 .... -- Thanx & Best Regards, Faraz! hi Faraz, On 01.03.2010 09:07, Faraz A. Qureshi wrote: > Now invoice numbers are different unique keys but not numeric, how to add a > fourth temporary column in a query to insert serial numbers as well like: > > Serial, DT, INVOICE, AMT > 1 > 2 > 3 > 4 Using DCount(): ...

Conditional formatting not quite working right
I have a form that is generated by a query. The query is populated via table. I want all values in the report that are over 1.99% to show up as red. Those values at 1.99% and lower should show up as green. IOW, percent values from 0 to 1.99% are good values and show green. Anything at 2% and over is bad, and should show up as red. I've created conditinal formatting in several different ways, using the options available under conditional formatting. It seems that no matter what I select, greater than, equal to, etc, all the values change to whatever the cond format is suppos...

Inserting text in a basic shape
In PowerPoint 2007, I've drawn several basic shapes, then clicked the shapes to add text; however, the text in several of the shapes is very strange...as though it's "outdented" on the first line or indented on the following lines. The lines are all out of alignment, and I've tried everything I can possibly think of but to no avail. Has anyone else experienced anything like this, and if so, do you know how to fix it? I know this will sound weird, but I suspect it's a printer driver issue. Please humor me and try this exact printer driver installed in t...

rule not working correctly
Hello, I have setup a few rules and some of them I have setup to Move the message to the following folder, yet it copies it to that folder. Is this a known bug? anyone know whats going on? The conditions are: on this machine only; through the specified account; then move to the specified folder. TIA Make sure you sort the rules correctly and add the "stop processing rules" action. This will prevent duplication when more than one rule applies. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office ...

compare numbers and mark duplicates
Hello all, I have a row of 9 cells, which I want to fill with the numbers 1 to 9. Each time I put a number in a cell I want excel to compare all 9 cells in the row. If I type in a number that is already in one of the other 8 cells I want it to light up in red. I tried using "conditional formatting" but I can only compare 2 cells at a time. Does anybody have an idea? Thanks in advance, Lucas try the countif function in your conditional formating "Lucas" wrote: > Hello all, > > I have a row of 9 cells, which I want to fill with the numbers 1 to 9. Each >...

money 2001 stopped working in vista
I have been using vista for about 4 months. installed money 2001 and had no problems for about 3 months. now it won't open or do anything. i've tried every suggestion i can find. reinstall, run as admin., compatibility mode, etc., but nothing works. whenever money is attempted to be opened, it thinks briefly and then just stops. nothing. no error messages or anything. money 2001 has worked fine for me and i really don't want to upgrade. any help would be much appreciated. I'm having exactly the same problem. Have uninstalled and reinstalled several times, with no suc...

Paste Special as Text
I have a small program that drops text from a Word table into Excel using Paste Special as Text. Although Paste Special as Text should insert the contents of the clipboard as text without any formatting this does not appear to be happening as it did with Excel 2000 . Excel 2003 splits the data when it meets a New Line or Carriage Return and drops anything after it down to row below. Any ideas what is causing this behaviour Thanks for your assistance Phil ...

putting text into pictures once in outlook
Hi I frequently want to send photos from outlook. Is thee an easy way of insertng text into pictures, tried text box, but it will not work. thanks tim "Tim" <adslk4at@tpg.com.au> wrote in message news:4241e306@dnews.tpgi.com.au... > Hi I frequently want to send photos from outlook. Is thee an easy way of > insertng text into pictures, tried text box, but it will not work. thanks > tim > Try a good image editing program. Outlook isn't one of those. ...

Extracting Partial Text from Field
A couple of weeks ago, I posted to this group for help extracting the To and By data from this field into two separate fields I could query and report on cs - Assigned Issue To: MARILYN MONROE Assigned by: JOHN KENNEDY I was given this and it worked GREAT: To: Mid([action_description],InStr([action_description],"Assigned Issue To:")+25,(InStrRev([action_description],"Assigned by:")-1)-(InStr([action_description],"Assigned Issue To:")+19)) I now have an Assigned To field that contains MARILYN MONROE and an Assigned By that contains ROBERT KENNEDY. The prob...

copy/paste excel to texteditor -> multi-line text cells gain unwanted double-quotes
i have a formula composed of 3 lines in one cell: =" IF c_MSISDN_NDC" & $E3 & " IS NOT NULL THEN utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_table)||'(c_new_point +" & $E3 & ", 1, c_oper_name);'); utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_table)||'(c_new_point +" & $E3 & ", 2, c_oper_name);');" this produces a text string, e.g. as below: " IF c_MSISDN_NDC70 IS NOT NULL THEN utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_t...

x-axis text wraping
I'm working with data in a column chart that expands over 36 periods.=20 Rather than have the x-axis list 1 thru 36 I want x-axis to display = text.=20 I already know how to get the text there and to adjust the x-axis=20 orientation, etc. However, the graph automatically wraps the text once=20 it gets so long.=20 I don't want the text to wrap. Is there anyway to have the chart not=20 wrap the text? Besides decreasing the font of the x-axis. Brett wrote: > I'm working with data in a column chart that expands over 36 periods. > Rather than have the x-axis list 1 thru 36 I wa...

InsertAfter does not work.
Hey Guys, I am trying to insert a xml elmement right after specific MainCategory element in the following xml file: <?xml version="1.0" encoding="utf-8"?> <CategoryList> <Category> <MainCategory ID="3">XML</MainCategory> <Description>List of XML articles.</Description> <Active>Yes</Active> </Category> <Category> <MainCategory ID="2">C#</MainCategory> <Description>List of C# articles.</Description> <Active>Yes</Active> </Category> &...

Enable software to work in Domain user account.
Hi to all Enable software to work in Domain user account. Software which i have installed in Win xp is i install software from domain administrator. 1. Office 2003 it's working 2. adobe PDF it's working Other software's are not working, issue in local user and domain user account only, No problem in administrator account (local and domain) if i give them administrator account i can't stop them from miss use the system. Note: Server OS: Win Server 2003 Client OS: Win xp, Win 7 Pro, Vista Business Regards, Vin Win On 03/07/10 03:37, Vinod ...

Outlook 10 Rule not fully work
I had assigned a rule: when the new email is arrive Forward to "b" except the email from "b" Its work but not all incoming email has been forward! It was a Outlook 2002 SP2 and pop3 client Do you have the same problem? Thanks! rgds, David ...