How to navigate within a formula without changing cell references?

I have the following function in G20:

  =IF(C20>0,F20,"")

I need to replace the second parameter (F20), with another IF
statement. 

  =IF(C20>0,IF(F20<>"",F20,"Error"),"")

I go to G20 and press F2 to edit the formula.

I move the cursor to just before the "F20" using the arrow keys and
start typing in the new IF statement. When I have it entered, I need
to move the cursor over the old F20 to finish the new IF statement,
but Excel takes the arrow keys to mean I want to reference an adjacent
cell. 

How can I get Excel to interpret the arrow keys only as cursor
movement and not cell reference movement?

0
SquarePeg (160)
9/10/2008 4:06:04 AM
excel 39879 articles. 2 followers. Follow

2 Replies
476 Views

Similar Articles

[PageSpeed] 2

hi, !

> I have the following function in G20:
>  =IF(C20>0,F20,"")
> I need to replace the second parameter (F20), with another IF statement.
>  =IF(C20>0,IF(F20<>"",F20,"Error"),"")
> I go to G20 and press F2 to edit the formula.
> I move the cursor to just before the "F20" using the arrow keys and start typing in the new IF statement.
> When I have it entered, I need to move the cursor over the old F20 to finish the new IF statement,
> but Excel takes the arrow keys to mean I want to reference an adjacent cell.
> How can I get Excel to interpret the arrow keys only as cursor movement and not cell reference movement?

any chance that while editing you are toggling {F2} key ?
check over the status bar, if it reads "modify...", arrow keys are for navigating within the formula
otherwise, you are selecting/navigating references on the worksheet    -?-

hth,
hector. 


0
9/10/2008 4:33:51 AM
On Tue, 9 Sep 2008 23:33:51 -0500, "H�ctor Miguel"
<NOhemiordiSPAM@PLShotmail.com> wrote:

>hi, !
>
>> I have the following function in G20:
>>  =IF(C20>0,F20,"")
>> I need to replace the second parameter (F20), with another IF statement.
>>  =IF(C20>0,IF(F20<>"",F20,"Error"),"")
>> I go to G20 and press F2 to edit the formula.
>> I move the cursor to just before the "F20" using the arrow keys and start typing in the new IF statement.
>> When I have it entered, I need to move the cursor over the old F20 to finish the new IF statement,
>> but Excel takes the arrow keys to mean I want to reference an adjacent cell.
>> How can I get Excel to interpret the arrow keys only as cursor movement and not cell reference movement?
>
>any chance that while editing you are toggling {F2} key ?
>check over the status bar, if it reads "modify...", arrow keys are for navigating within the formula
>otherwise, you are selecting/navigating references on the worksheet    -?-

I was not toggling F2, but your question led me to the answer.

When I first press F2 to edit the formula, the status bar reads
"Edit". If I press F2 again, it changes to "Enter". Repeated use of F2
will toggle it between Edit and Enter.

I am using Excel 2007, so they may have changed "Modify" to "Enter".

When I click on another cell to insert it as a cell reference, it
changes to "Point", which behaves just like "Enter". The solution is
to press F2 again, which chanhges it back to Edit and I can now use
the arrow keys to navigate.

Thanks
0
SquarePeg (160)
9/10/2008 5:22:13 AM
Reply:

Similar Artilces:

Pasting into an active cell
I have some VBA code (see below) where I am copying a fixed selection from one workbook (which the user has selected), into the next empty row in a different workbook (where the macro is located). Sheets("National").Select Range("A6:X21").Select Selection.Copy Windows("Diagnostics 2010-2011.xls").Activate Sheets("C-National").Select Selection.paste However, I get a run time error (438) on the paste command when I try to run it. Any suggestions, as I am new to VBA Try the below which will copy the range from the Activeworkbook Sh...

Month Formula
Please advise me on how to write a formula that will start with January 2003, when I copy it to the next row it will increase the month to February and so on. When I get to December 2003, the following month will be January 2004. Thank You You don't need a formula: Enter January 2003 in A1 and February 2003 in A2. Select A1:A2 and drag the fill handle (lower right corner of A2) down as far as necessary. XL will autofill. If you want a formula: A1: =DATE(2003,ROW(A1),1) format as "mmmm yyyy". This will give you actual dates (e.g., 1/1/2003, 2/1/2003). I...

Navigating through cells
I want to create a form and want to skip cells: Like navitgate from A1 to F8 in one stroke. Can I do that? Gram I think you want the form to just be a spreadsheet. In that case select the celss that you want to re-use for data entry and choose, Format, Cells and on the protection tab deselect the Locked checkmark and click OK Now choose Tools, Protection and protect the worksheet. You should be able to use the Tab key to jump between data entry cells. Now save the workbook. Remember that if you apply a password it is Case Sensitive and you will be unable to use the worksheet if you...

Default printer changing
A user at the office has following issue. When he returns home with his Dell Laptop his USB home printer is set as default even when it's not connected. When he arrives at the office he has to change his network printer as the default printer every time. Altough this one is configured as the default printer. I know that there are some scripts who changes the default printer, but I'm 100% sure that he hasn't had the issue in the past with his former laptop. Both laptops had Windows XP both laptops are Dell. Can someone help me out please? -- fred_michiels ...

summing formula help
I have a column that could produce 1 row of data or 35 rows of data. How can i consistently sum two fields below last cell with data and have everything included. thanks Can you provide a sample of your data and expected results? In article <4606BA0E-1138-4F87-BB4C-0F442DC03BC9@microsoft.com>, "jerry" <jerry@discussions.microsoft.com> wrote: > I have a column that could produce 1 row of data or 35 rows of data. How can > i consistently sum two fields below last cell with data and have everything > included. > > thanks Sheet 1 ...

Formula Help 04-28-10
I need a formula that will take A1 x B1 but if the number is less then 0 it will only how up as 0 and if its more then 0 if will show up as the real number. "Rebecca" wrote: > I need a formula that will take A1 x B1 but if the > number is less then 0 it will only how up as 0 and > if its more then 0 if will show up as the real number. Try: =max(0, A1*B1) PS: For the future, it would be wise to choose a more distinctive subject; e.g. "Multiply But No Less Than Zero?". By choosing such a non-descript title, your posting might get lost as a r...

Cells containing lots of text will not wrap?
Hi, I have a report to complete on an excel sheet. Some of the cells contain lots of text and no matter how I format the cell I can't get it to show me all of the text. It behaves fine up to a certain point, line wrapping as it should but then it reaches a point where the text just disappears from view. It's still there in the cell but just not visible. Manually increasing the size of the cell has no effect. This is driving me nuts! -- Regards Jon If you add alt-enters to force new lines within the cell (every 80-100 characters), then you'll be able to see more characters...

Using Frequency formula
hi, im trying to figure out the frequency of a sample of students final marks and this is the formula im using '=FREQUENCY(FinalMarks,B11:B12)' but as i go from B11:B12 to B12:B13 it accumulates the score instead of just counting the frequency of B12:B13. how do i adjust the formula so it doesn't accumulate thanks What is FinalMarks. I presume you have named the range. Frequency is an array formula, so no need to drag down. Check the help on its usage. You need to select the entire output area and enter the formula, and press ctrl shft enter Mangesh "jimbo" <j...

set value of variable from cell
I'm trying to check cells within a column. Check a2, then a4, then a6 then a8, etc. If the cell is blank, test the next in the series. When a cell is not blank, take the value from that cell and place it into a variable. If the columns are dates, like Column A is the 1st... I want the value of the first filled cell in column A to be placed into a variable like var1st which has been publicly declared as string. Is that enough info? I'm running Excel 2002. Thanks! ...

Money 2005
In Money 2004 there was a lot of flexability in the information displayed for individual stocks in a portfolio. I have been unable to find much flexability in the display of stock info in Money 2005. For example in Money 2004 I had the number of shares of a particular stock multiplied by the change in the stocks value for that day to arrive at the dollar amount of change in a particular stock. I can't seem to find this ability in Money 2005. Can you help? In microsoft.public.money, jwd wrote: >In Money 2004 there was a lot of flexability in the information displayed for >i...

Application navigation
1.Whenever i navigate my MFC application to another application ,all dialog boxes are displaying in another application also.My dialog box style(property) is popup if i make style property as CHILD it is not at all displaying. How can i solve this problem. 2. Once i minimise my MFC application If i try to maximize by clicking or pressing alt + tab my MFC application is not maximising , i used OnActivate event in frame window inside i have handled WA_ACTIVE and WA_CLICKACTIVE. 3.To refresh my application how can i get all control hanles for example frame window, dialog box etc. ...

Making portion of formula BOLDFACE
I have a text formula which ends up being way greater than the 256 character limit. I am using Excel 97. The first 2/3rds of the formula a static piece of text of almost 700 characters. To that by formula I am appending one of two sentences. Which ever piece is appended must be in bold (a legal requirement). I can't figure out how to make that last piece bold. Is there a function I can use for the conditional text? Do I need to do this by a macro since I will know the length of the first portion which has no conditions? A cell containing a formula cannot have formatting applied to pa...

X Axis Change from 97 to 2K
My boss was using Excel 97 and just got a new computer with Excel 2K. When he tried to make a chart of Temperature readings in Excel 2K, the x axis turned out totally black. Take a look at these pictures to see what is going on. Please look at the date on the x axis. Both charts were created with the same data and same options. The first was created in Excel 97: http://home.comcast.net/~keebird/excel_97.jpg The second was created in Excel 2K: http://home.comcast.net/~keebird/excel_2k.jpg Here is the data that was used for both charts: http://home.comcast.net/~keebird/data.jpg We ha...

Formulas don't work anymore. PLEASE HELP!
I have a detailed spreadsheet that has multiple formulas. All the formulas worked for the last two years. Now, the formulas stopped working unless I hit F2 to edit the cell with the formula and enter. thanks in advance. Hi goto 'tools - Options - Calculate' and check 'Autmatic calculation' -- Regards Frank Kabel Frankfurt, Germany "KT" <ktdev@hotmail.com> schrieb im Newsbeitrag news:OKiaH5cmEHA.3352@TK2MSFTNGP10.phx.gbl... > I have a detailed spreadsheet that has multiple formulas. All the formulas > worked for the last two years. Now, the formul...

Clicking on Web Links within Outlook 2000 doesn't work
Good Morning, I have a workstation with MS Outlook 2000, Win XP. When you click on a web link within an e-mail message, it opens Internet Explorer, however it gives the page not found error: "Cannot Find: 'D:\http://www.website.com'" It seems that the system is trying to look for the website on my D Drive instead of the internet. This problem occurs in every e-mail message and link. I don't know if this is related, but in the address bar of IE, instead of having the little IE icon in front of the web address, there is a windows icon, which is telling me someth...

How to change initial username?
I have a few excel files that are shared among a few users. When the same file are in used by more than 1 user, a message box will promt that "user A" is using the file and gives the option "read-only", "notify" and "cancel". Question is, how do i change the username in the message box? As far as i know, you can only change the username when we install Microsoft Office. Change the User Name on: Tools|Options|General Tab EeVee wrote: > > I have a few excel files that are shared among a few users. When the same > file are in used by more th...

Open in New Window from Navigation Pane
Right clicking on Calendar button, selecting "open in new window" I am unable to set the new window to open maximized or at least fill the screen. Many attempts at closing the window to save settings; shift + X, space bar + close, etc yield no results. New window currently opens so small as to be unreadable. -- John Set the main Outlook window to it's prefered size in Normal size and use CTRL+X to force the save of the windowsize. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slips...

Formula to Subtract to a Limit ?
I need some way to subtract column b from column a... but I do not want the result to go below zero. Ex 200-100=100 200-300 = 0 (reached zero lower limit) Is there an excel formula... or perhaps some way to program a little code to do this? If it's programming, give me a couple clues on how to enter the code - I'm a programmer but don't know much about Excel aside from a bit of advanced formula entry. Thanks, =MAX(A1-B1,0) and copy down the column -- Gary''s Student "Bob" wrote: > I need some way to subtract column b from column a... but I do no...

Navigation problems
This is embarrassing to write, because I am the go-to guy in our office for almost everything, and I have something I can't solve myself. At home, I have Excel 2002, but at the office, I have Excel 2003. For some unknown reason, I am having problems navigating my spreadsheets. I am an old, old DOS veteran, who is used to keyboard shortcuts, and in one, I am entering a series of values, with the cursor set to jump to the right after each {enter}. At the last, I expect to hit {home} and go to Column A. Unfortunately, in this application, {home} seems to mean "go to upper left h...

Dials connection without prompting
Whenever I select an email that has links to the web for the content (e.g. ad emails from Dell) Outlook automatically dials the dial-up networking connection to get the content. It used to prompt me to ask if I wanted to dial up in this circumstance. How do I reset it so that it does prompt me? Thanks, Richard. richard <rscott@tesco.net> wrote: > Whenever I select an email that has links to the web for > the content (e.g. ad emails from Dell) Outlook > automatically dials the dial-up networking connection to > get the content. > > It used to prompt me to ask...

Cell contents Prefixing
I have a simple question. I have a column in a spreadsheet where some rows start with the letter Q, and some do not. I'd like to update the rows so that column is prefixed with the letter Q on every row. Is there a way to do this? Assuming your date is in column A. One way would be to use a helper column and this formula copied down....then do Copy > PasteSpecial > Values on the helper column to get rid of the formulas and replace column A with the helper column........ =IF(LEFT(A1,1)="Q",A1,"Q"&A1) Vaya con Dios, Chuck, CABGx3 "HockeyFan&qu...

change user on each record
It would be nice to record the last changed date and the user who changed each record on ALL records, even if a history of changes is not being kept. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbro...

Why do cells skip around when I am trying to input figure within
I was doing payroll figures on Excell 2003. Suddenly I could not input figures within a cell and when I tried typing in the figure it would jump me to other cells, but no info input Probably the NUMLOCK key got toggled, so the keypad went into the cursor move mode. -- Gary''s Student "Query mind in Tucson" wrote: > I was doing payroll figures on Excell 2003. Suddenly I could not input > figures within a cell and when I tried typing in the figure it would jump me > to other cells, but no info input ...

Updating grouped cells
I am trying to select only the lowest level cells in a spreadsheet with grouped columns. The spreadsheet has cost centres grouped into departments. The departments are grouped into divisions. The cost centres get their values from other sheets and workbooks. I would like to be able to change the formulas in the cost centre columns only - without disturbing the sum formulas in the department and division columns. Is there a quick way to select only the cost centre cells in a row without selecting them individually? Probably not. But if you have to do that frequently, why not select the c...

Changing Title Bar
Hello, I developed an ActiveX control. Within the ActiveX control I display a message box using AfxMessageBox(). However, the title of the message box is the partial ActiveX control name. How do I change the title bar of the message box? TIA, Jacques Use ::MessageBox(..) API instead of AfxMessageBox(). The other option is to override the DoMessageBox() member of your App class and change the title. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Jacques Cooper" <jcooper@jcsoftware.net> wrote in message news:OBfS5yDwDHA.1196@TK2MSFTNGP12.phx....