H
Harlan Grove
Excel 2003 SP2.
I've been revising a workbook and came across an Excel quirk that's
new to me. I haven't read about anything similar in the newsgroups.
The workbook in question usually has all worksheets protected and the
workbook protected, but I've unprotected the workbook and all
worksheets and unhidden all but one worksheet. Some of the worksheets
have embedded spaces in their names. There are no worksheet events,
but there are workbook Open, Activate, Deactivate and BeforeClose
events. There's also one udf, named HasFormula, that takes a Range
object as its argument and returns TRUE/FALSE if the first cell in
that range has a formula. I only use this udf in conditional
formatting formulas.
I'm editing formulas in one of the worksheets that's usually hidden.
Call it Sheet C. The formulas were something like
='Sheet B'!X99
and I want to change them to
=IF('Sheet B'!X99<>"",'Sheet B'!X99,'Sheet A'!C123)
I edit the formula in a cell in Sheet C, inserting the 'IF(' at the
beginning, copying the original cell reference in the formula bar,
appending '<>"",', pasting the cell reference, and appending a comma.
Then I press [F2] to change to Enter mode, repeatedly press [Ctrl]+
[PgUp] to move to 'Sheet A', move to cell C123 in that worksheet (now
in Point mode), type a right parenthesis and then press [Enter].
Here's the quirk: Excel tries to enter the edited formula into 'Sheet
A'!C123 rather than into the cell in Sheet C that I began editing.
Fortunately, 'Sheet A'!C123 has a validation rule that rejects the
formula. I should also mention that Sheet A has separate frozen panes,
and C123 is in the lower, unfrozen pane.
Has anyone else had Excel do this? I can work around it, so I'm not
looking for an immediate fix, but this is definitely annoying. If
there's an easy fix, I'd be grateful for any pointers.
I've been revising a workbook and came across an Excel quirk that's
new to me. I haven't read about anything similar in the newsgroups.
The workbook in question usually has all worksheets protected and the
workbook protected, but I've unprotected the workbook and all
worksheets and unhidden all but one worksheet. Some of the worksheets
have embedded spaces in their names. There are no worksheet events,
but there are workbook Open, Activate, Deactivate and BeforeClose
events. There's also one udf, named HasFormula, that takes a Range
object as its argument and returns TRUE/FALSE if the first cell in
that range has a formula. I only use this udf in conditional
formatting formulas.
I'm editing formulas in one of the worksheets that's usually hidden.
Call it Sheet C. The formulas were something like
='Sheet B'!X99
and I want to change them to
=IF('Sheet B'!X99<>"",'Sheet B'!X99,'Sheet A'!C123)
I edit the formula in a cell in Sheet C, inserting the 'IF(' at the
beginning, copying the original cell reference in the formula bar,
appending '<>"",', pasting the cell reference, and appending a comma.
Then I press [F2] to change to Enter mode, repeatedly press [Ctrl]+
[PgUp] to move to 'Sheet A', move to cell C123 in that worksheet (now
in Point mode), type a right parenthesis and then press [Enter].
Here's the quirk: Excel tries to enter the edited formula into 'Sheet
A'!C123 rather than into the cell in Sheet C that I began editing.
Fortunately, 'Sheet A'!C123 has a validation rule that rejects the
formula. I should also mention that Sheet A has separate frozen panes,
and C123 is in the lower, unfrozen pane.
Has anyone else had Excel do this? I can work around it, so I'm not
looking for an immediate fix, but this is definitely annoying. If
there's an easy fix, I'd be grateful for any pointers.