Editing long conditional formatting formulae

[markly]

In Excel97, I have longer conditional formatting formulae that can be displayed in the formula window, and I need to edit them. But when I move the cursor to the end of the window to get the next bit of formula, the cursor shoots to the end of the formula, and I can't get the middle piece to be displayed for editing it.

The formulae are like this:
=AND(Z6=8,(COUNTIF(A6:CC6,8)+COUNTIF(Z2,8)+COUNTIF(Z4,8)+COUNTIF(Z8,8)+COUNTIF(Z10,8)+COUNTIF(Z12,8)+COUNTIF(Z14,8)+COUNTIF(Z16,8)+COUNTIF(Z18,8)+COUNTIF(H2,8)+COUNTIF(H4,8)+COUNTIF(Q2,8)+COUNTIF(Q4,8))=1)

That is, if the value of Z6 is '8', and it is the only 8 in row6 from cols A to CC, and the only 8 in (1) rows 2, 4, 8, 10, 12, 14, 16 and 18 of col Z, (2) rows 2 and 4 of col H, and (3) rows 2 and 4 of col Q, then format Z6.

Using the right arrow to move along the formula, even with Ctrl or Alt, doesn't work. It simply puts in unwanted cell references wherever the cursor line happens to be.

Expanding the window by clicking the symbol at the right does not give me the entire formula in two windows.

Is there some way of moving the cursor line along the formula, character by character, so that every element of the formula can be checked/edited within the formula window?

markly

 

[VoG]

A bit fiddly but you should be able to highlight the entire formula to select it. Press CTRL + C to copy it, paste into Notepad and edit it. Then copy the formula from Notepad and use CTRL + V to paste it into the CF formula bar.

I've tried it in XL 2000 and it works.

 

[GlennUK]

If you want to use the arrows to navigate the formula without it inserting references then press F2 before you try it.

 

[Lewiy]

Don’t know if this will help, but you can shorten your formula a bit:

=AND(Z6=8,(COUNTIF(A6:CC6,8)+SUMPRODUCT(--(Z2:Z18=8),ROW(Z2:Z18))-6)+(OR(H2=8,H4=8,Q2=8,Q4=8))=1)

Best way to edit though would just be to write the formula somewhere else and copy&paste it back in.

 

[Peter_SSs]

Just a little clarification to Glenn's suggestion. Whan you bring up the Conditional Formatting dialog, click in the formula first to get your cursor in there, then press F2. Then you should be able to use right/left arrows to move arround the formula without inserting strange cell references.

 

[markly]

Thanks Glenn and Peter. Pressing F2 once the cursor line is in the formula, then using the arrow key, is definitely the solution. I knew there just had to be a simpler way than copying the formula somewhere else, editing it there, then sticking it back.