Conditional Formula to indicate Formula in cell

[SteveW]

I've got a formula in a column, but in certain circumstances I may =

overwrite this formula with a number (too rare a special case to bother =
=

covering)

What I'd like though is to use Conditional Formatting to alter cell to =

Bold or something to show that this is *a manual override*

Can't see how you can check for formula present ?

Any ideas ?

-- =

Steve (3)

[Bob Phillips]

You need a UDF and use that

Function IsFormula(rng As Range)
IsFormula = rng.HasFormula
End Function

and use that in the CF.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"SteveW" <[email protected]> wrote in message
news:op.tdl33kuqevjsnp@enigma03...

I've got a formula in a column, but in certain circumstances I may
overwrite this formula with a number (too rare a special case to bother
covering)

What I'd like though is to use Conditional Formatting to alter cell to
Bold or something to show that this is *a manual override*

Can't see how you can check for formula present ?

Any ideas ?

--
Steve (3)

[Ron Coderre]

My preference would be to have an Override Cell next to the formula cell.
Dependent formulas would use the formula if the Override Cell is blank.

But...regarding what you asked for, try this:

From the Excel main menu:
<insert><name><define>
Names in Workbook: IsFormula
Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))

Note: Get.Cell is from the old Excel macro function language. The 48
argument makes it return TRUE for formulas (otherwise: FALSE)

Caution: I seem to recall GET.CELL might cause an issue in XL97 or XL2000 (I
can't remember). So, save first, before you try this technique.

Next...
Select the cell that will either contain a formula or a constant.
<format><conditional formatting>
Formula Is: =(IsFormula=FALSE)
then...Click the [formatting] button and adjust the format and you're done

If the cell contains a constant....the conditional format will display.
Otherwise, the default format will be used.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"SteveW" wrote:

>
> I've got a formula in a column, but in certain circumstances I may
> overwrite this formula with a number (too rare a special case to bother
> covering)
>
> What I'd like though is to use Conditional Formatting to alter cell to
> Bold or something to show that this is *a manual override*
>
> Can't see how you can check for formula present ?
>
> Any ideas ?
>
> --
> Steve (3)
>

[Bob Phillips]

You need a UDF and use that

Function IsFormula(rng As Range)
IsFormula = rng.HasFormula
End Function

and use that in the CF.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"SteveW" <[email protected]> wrote in message
news:op.tdl33kuqevjsnp@enigma03...

I've got a formula in a column, but in certain circumstances I may
overwrite this formula with a number (too rare a special case to bother
covering)

What I'd like though is to use Conditional Formatting to alter cell to
Bold or something to show that this is *a manual override*

Can't see how you can check for formula present ?

Any ideas ?

--
Steve (3)

[Ron Coderre]

My preference would be to have an Override Cell next to the formula cell.
Dependent formulas would use the formula if the Override Cell is blank.

But...regarding what you asked for, try this:

From the Excel main menu:
<insert><name><define>
Names in Workbook: IsFormula
Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))

Note: Get.Cell is from the old Excel macro function language. The 48
argument makes it return TRUE for formulas (otherwise: FALSE)

Caution: I seem to recall GET.CELL might cause an issue in XL97 or XL2000 (I
can't remember). So, save first, before you try this technique.

Next...
Select the cell that will either contain a formula or a constant.
<format><conditional formatting>
Formula Is: =(IsFormula=FALSE)
then...Click the [formatting] button and adjust the format and you're done

If the cell contains a constant....the conditional format will display.
Otherwise, the default format will be used.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"SteveW" wrote:

>
> I've got a formula in a column, but in certain circumstances I may
> overwrite this formula with a number (too rare a special case to bother
> covering)
>
> What I'd like though is to use Conditional Formatting to alter cell to
> Bold or something to show that this is *a manual override*
>
> Can't see how you can check for formula present ?
>
> Any ideas ?
>
> --
> Steve (3)
>

[CLR]

That's cool Ron..........most cool. It works fine in XL97 SR2.

Vaya con Dios,
Chuck, CABGx3



"Ron Coderre" wrote:

> My preference would be to have an Override Cell next to the formula cell.
> Dependent formulas would use the formula if the Override Cell is blank.
>
> But...regarding what you asked for, try this:
>
> From the Excel main menu:
> <insert><name><define>
> Names in Workbook: IsFormula
> Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))
>
> Note: Get.Cell is from the old Excel macro function language. The 48
> argument makes it return TRUE for formulas (otherwise: FALSE)
>
> Caution: I seem to recall GET.CELL might cause an issue in XL97 or XL2000 (I
> can't remember). So, save first, before you try this technique.
>
> Next...
> Select the cell that will either contain a formula or a constant.
> <format><conditional formatting>
> Formula Is: =(IsFormula=FALSE)
> then...Click the [formatting] button and adjust the format and you're done
>
> If the cell contains a constant....the conditional format will display.
> Otherwise, the default format will be used.
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "SteveW" wrote:
>
> >
> > I've got a formula in a column, but in certain circumstances I may
> > overwrite this formula with a number (too rare a special case to bother
> > covering)
> >
> > What I'd like though is to use Conditional Formatting to alter cell to
> > Bold or something to show that this is *a manual override*
> >
> > Can't see how you can check for formula present ?
> >
> > Any ideas ?
> >
> > --
> > Steve (3)
> >

[Ron Coderre]

Thanks, Chuck....
I saw that technique a some time ago. I want to say it's from David
McRitchie..(but, I'm not sure).


***********
Best Regards,
Ron

XL2002, WinXP


"CLR" wrote:

> That's cool Ron..........most cool. It works fine in XL97 SR2.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Ron Coderre" wrote:
>
> > My preference would be to have an Override Cell next to the formula cell.
> > Dependent formulas would use the formula if the Override Cell is blank.
> >
> > But...regarding what you asked for, try this:
> >
> > From the Excel main menu:
> > <insert><name><define>
> > Names in Workbook: IsFormula
> > Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))
> >
> > Note: Get.Cell is from the old Excel macro function language. The 48
> > argument makes it return TRUE for formulas (otherwise: FALSE)
> >
> > Caution: I seem to recall GET.CELL might cause an issue in XL97 or XL2000 (I
> > can't remember). So, save first, before you try this technique.
> >
> > Next...
> > Select the cell that will either contain a formula or a constant.
> > <format><conditional formatting>
> > Formula Is: =(IsFormula=FALSE)
> > then...Click the [formatting] button and adjust the format and you're done
> >
> > If the cell contains a constant....the conditional format will display.
> > Otherwise, the default format will be used.
> >
> > Is that something you can work with?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "SteveW" wrote:
> >
> > >
> > > I've got a formula in a column, but in certain circumstances I may
> > > overwrite this formula with a number (too rare a special case to bother
> > > covering)
> > >
> > > What I'd like though is to use Conditional Formatting to alter cell to
> > > Bold or something to show that this is *a manual override*
> > >
> > > Can't see how you can check for formula present ?
> > >
> > > Any ideas ?
> > >
> > > --
> > > Steve (3)
> > >

[SteveW]

I must have asked before - memory's gone
But I remember something like that

Cheers


On Tue, 01 Aug 2006 17:30:08 +0100, Bob Phillips <[email protected]> wrote=
:

> You need a UDF and use that
>
> Function IsFormula(rng As Range)
> IsFormula =3D rng.HasFormula
> End Function
>
> and use that in the CF.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "SteveW" <[email protected]> wrote in message
> news:op.tdl33kuqevjsnp@enigma03...
>
> I've got a formula in a column, but in certain circumstances I may
> overwrite this formula with a number (too rare a special case to bothe=
r
> covering)
>
> What I'd like though is to use Conditional Formatting to alter cell to=

> Bold or something to show that this is *a manual override*
>
> Can't see how you can check for formula present ?
>
> Any ideas ?
>
> --
> Steve (3)
>
>



-- =

Steve (3)

[SteveW]

That works as well, but you do have to put it into every workbook

Steve

On Tue, 01 Aug 2006 17:32:02 +0100, Ron Coderre =

<[email protected]> wrote:

> My preference would be to have an Override Cell next to the formula ce=
ll.
> Dependent formulas would use the formula if the Override Cell is blank=
..
>
> But...regarding what you asked for, try this:
>
> From the Excel main menu:
> <insert><name><define>
> Names in Workbook: IsFormula
> Refers to: =3DGET.CELL(48,INDIRECT("RC",FALSE))
>
> Note: Get.Cell is from the old Excel macro function language. The 48
> argument makes it return TRUE for formulas (otherwise: FALSE)
>
> Caution: I seem to recall GET.CELL might cause an issue in XL97 or =

> XL2000 (I
> can't remember). So, save first, before you try this technique.
>
> Next...
> Select the cell that will either contain a formula or a constant.
> <format><conditional formatting>
> Formula Is: =3D(IsFormula=3DFALSE)
> then...Click the [formatting] button and adjust the format and you're =
=

> done
>
> If the cell contains a constant....the conditional format will display=
..
> Otherwise, the default format will be used.
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "SteveW" wrote:
>
>>
>> I've got a formula in a column, but in certain circumstances I may
>> overwrite this formula with a number (too rare a special case to both=
er
>> covering)
>>
>> What I'd like though is to use Conditional Formatting to alter cell t=
o
>> Bold or something to show that this is *a manual override*
>>
>> Can't see how you can check for formula present ?
>>
>> Any ideas ?
>>
>> --
>> Steve (3)
>>



-- =

Steve (3)

[Ron Coderre]

Yes...you'd need to create it in each workbook that will use it.

***********
Regards,
Ron

XL2002, WinXP


"SteveW" wrote:

> That works as well, but you do have to put it into every workbook
>
> Steve
>
> On Tue, 01 Aug 2006 17:32:02 +0100, Ron Coderre
> <[email protected]> wrote:
>
> > My preference would be to have an Override Cell next to the formula cell.
> > Dependent formulas would use the formula if the Override Cell is blank..
> >
> > But...regarding what you asked for, try this:
> >
> > From the Excel main menu:
> > <insert><name><define>
> > Names in Workbook: IsFormula
> > Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))
> >
> > Note: Get.Cell is from the old Excel macro function language. The 48
> > argument makes it return TRUE for formulas (otherwise: FALSE)
> >
> > Caution: I seem to recall GET.CELL might cause an issue in XL97 or
> > XL2000 (I
> > can't remember). So, save first, before you try this technique.
> >
> > Next...
> > Select the cell that will either contain a formula or a constant.
> > <format><conditional formatting>
> > Formula Is: =(IsFormula=FALSE)
> > then...Click the [formatting] button and adjust the format and you're
> > done
> >
> > If the cell contains a constant....the conditional format will display..
> > Otherwise, the default format will be used.
> >
> > Is that something you can work with?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "SteveW" wrote:
> >
> >>
> >> I've got a formula in a column, but in certain circumstances I may
> >> overwrite this formula with a number (too rare a special case to bother
> >> covering)
> >>
> >> What I'd like though is to use Conditional Formatting to alter cell to
> >> Bold or something to show that this is *a manual override*
> >>
> >> Can't see how you can check for formula present ?
> >>
> >> Any ideas ?
> >>
> >> --
> >> Steve (3)
> >>
>
>
>
> --
> Steve (3)
>

[Ragdyer]

Hey Guys,
FWIW

Just went through this a few days ago with a couple of OPs where I suggested
another type of "Get.Cell" formula.

CAVEAT:
IN *PRE* XL02 versions ... COPYING this type (GET.CELL) of formula between
WBs *will* cause XL to CRASH ! ! !

The bug was fixed in XL02 forward.

It is OK to *use* in any version.
Just create a new formula in each new or additional WB, just *don't* copy
it.

If you anticipate users attempting to copy on their own, *don't* chance
using this!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> Yes...you'd need to create it in each workbook that will use it.
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "SteveW" wrote:
>
>> That works as well, but you do have to put it into every workbook
>>
>> Steve
>>
>> On Tue, 01 Aug 2006 17:32:02 +0100, Ron Coderre
>> <[email protected]> wrote:
>>
>> > My preference would be to have an Override Cell next to the formula
>> > cell.
>> > Dependent formulas would use the formula if the Override Cell is
>> > blank..
>> >
>> > But...regarding what you asked for, try this:
>> >
>> > From the Excel main menu:
>> > <insert><name><define>
>> > Names in Workbook: IsFormula
>> > Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))
>> >
>> > Note: Get.Cell is from the old Excel macro function language. The 48
>> > argument makes it return TRUE for formulas (otherwise: FALSE)
>> >
>> > Caution: I seem to recall GET.CELL might cause an issue in XL97 or
>> > XL2000 (I
>> > can't remember). So, save first, before you try this technique.
>> >
>> > Next...
>> > Select the cell that will either contain a formula or a constant.
>> > <format><conditional formatting>
>> > Formula Is: =(IsFormula=FALSE)
>> > then...Click the [formatting] button and adjust the format and you're
>> > done
>> >
>> > If the cell contains a constant....the conditional format will
>> > display..
>> > Otherwise, the default format will be used.
>> >
>> > Is that something you can work with?
>> > ***********
>> > Regards,
>> > Ron
>> >
>> > XL2002, WinXP
>> >
>> >
>> > "SteveW" wrote:
>> >
>> >>
>> >> I've got a formula in a column, but in certain circumstances I may
>> >> overwrite this formula with a number (too rare a special case to
>> >> bother
>> >> covering)
>> >>
>> >> What I'd like though is to use Conditional Formatting to alter cell to
>> >> Bold or something to show that this is *a manual override*
>> >>
>> >> Can't see how you can check for formula present ?
>> >>
>> >> Any ideas ?
>> >>
>> >> --
>> >> Steve (3)
>> >>
>>
>>
>>
>> --
>> Steve (3)
>>

[Ron Coderre]

Thanks, RD....I had vague recollections of there being some kind of problem
with the GET.CELL function. So many changes over the years. (I've been
working with spreadsheets since VisiCalc.)

***********
Regards,
Ron

XL2002, WinXP


"Ragdyer" wrote:

> Hey Guys,
> FWIW
>
> Just went through this a few days ago with a couple of OPs where I suggested
> another type of "Get.Cell" formula.
>
> CAVEAT:
> IN *PRE* XL02 versions ... COPYING this type (GET.CELL) of formula between
> WBs *will* cause XL to CRASH ! ! !
>
> The bug was fixed in XL02 forward.
>
> It is OK to *use* in any version.
> Just create a new formula in each new or additional WB, just *don't* copy
> it.
>
> If you anticipate users attempting to copy on their own, *don't* chance
> using this!
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Ron Coderre" <[email protected]> wrote in message
> news:[email protected]...
> > Yes...you'd need to create it in each workbook that will use it.
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "SteveW" wrote:
> >
> >> That works as well, but you do have to put it into every workbook
> >>
> >> Steve
> >>
> >> On Tue, 01 Aug 2006 17:32:02 +0100, Ron Coderre
> >> <[email protected]> wrote:
> >>
> >> > My preference would be to have an Override Cell next to the formula
> >> > cell.
> >> > Dependent formulas would use the formula if the Override Cell is
> >> > blank..
> >> >
> >> > But...regarding what you asked for, try this:
> >> >
> >> > From the Excel main menu:
> >> > <insert><name><define>
> >> > Names in Workbook: IsFormula
> >> > Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))
> >> >
> >> > Note: Get.Cell is from the old Excel macro function language. The 48
> >> > argument makes it return TRUE for formulas (otherwise: FALSE)
> >> >
> >> > Caution: I seem to recall GET.CELL might cause an issue in XL97 or
> >> > XL2000 (I
> >> > can't remember). So, save first, before you try this technique.
> >> >
> >> > Next...
> >> > Select the cell that will either contain a formula or a constant.
> >> > <format><conditional formatting>
> >> > Formula Is: =(IsFormula=FALSE)
> >> > then...Click the [formatting] button and adjust the format and you're
> >> > done
> >> >
> >> > If the cell contains a constant....the conditional format will
> >> > display..
> >> > Otherwise, the default format will be used.
> >> >
> >> > Is that something you can work with?
> >> > ***********
> >> > Regards,
> >> > Ron
> >> >
> >> > XL2002, WinXP
> >> >
> >> >
> >> > "SteveW" wrote:
> >> >
> >> >>
> >> >> I've got a formula in a column, but in certain circumstances I may
> >> >> overwrite this formula with a number (too rare a special case to
> >> >> bother
> >> >> covering)
> >> >>
> >> >> What I'd like though is to use Conditional Formatting to alter cell to
> >> >> Bold or something to show that this is *a manual override*
> >> >>
> >> >> Can't see how you can check for formula present ?
> >> >>
> >> >> Any ideas ?
> >> >>
> >> >> --
> >> >> Steve (3)
> >> >>
> >>
> >>
> >>
> >> --
> >> Steve (3)
> >>
>
>