Conditional Formula to indicate Formula in cell

  • Thread starter Thread starter SteveW
  • Start date Start date
S

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 ?
 
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)


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 ?
 
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
 
That's cool Ron..........most cool. It works fine in XL97 SR2.

Vaya con Dios,
Chuck, CABGx3



Ron Coderre said:
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 said:
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 ?
Click to expand...
Click to expand...
 
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 said:
That's cool Ron..........most cool. It works fine in XL97 SR2.

Vaya con Dios,
Chuck, CABGx3



Ron Coderre said:
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 said:
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 ?
Click to expand...
Click to expand...
Click to expand...
 
That works as well, but you do have to put it into every workbook

Steve

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 said:
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 ?
Click to expand...
Click to expand...
 
Yes...you'd need to create it in each workbook that will use it.

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

XL2002, WinXP


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

Steve

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 said:
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 ?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Yes...you'd need to create it in each workbook that will use it.

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

XL2002, WinXP


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

Steve

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


:


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 ?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Yes...you'd need to create it in each workbook that will use it.

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

XL2002, WinXP


SteveW said:
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

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


:


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 ?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top