Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formula to indicate Formula in cell
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) |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formula to indicate Formula in cell
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" 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) |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formula to indicate Formula in cell
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) |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formula to indicate Formula in cell
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) |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formula to indicate Formula in cell
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) |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formula to indicate Formula in cell
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 wrote: 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" 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) -- Steve (3) |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formula to indicate Formula in cell
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 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) |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formula to indicate Formula in cell
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 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) |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formula to indicate Formula in cell
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" wrote in message ... 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 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) |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formula to indicate Formula in cell
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" wrote in message ... 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 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting if cell content is a formula | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Deferring conditional formatting? | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |