Conditional Formula to indicate Formula in cell SteveW <sj_walton[ at ]nothotmail.com> 01.08.2006 16:00:46 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)

Re: Conditional Formula to indicate Formula in cell "Bob Phillips" <bob.NGs[ at ]xxxx.om> 01.08.2006 16:30:08 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" <sj_walton[ at ]nothotmail.com> wrote in message news:op.tdl33kuqevjsnp[ at ]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)

RE: Conditional Formula to indicate Formula in cell Ron Coderre 01.08.2006 16:32:02 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: [Quoted Text] > > 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) >

RE: Conditional Formula to indicate Formula in cell CLR 01.08.2006 16:54:02 That's cool Ron..........most cool. It works fine in XL97 SR2. Vaya con Dios, Chuck, CABGx3 "Ron Coderre" wrote: [Quoted Text] > 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) > >

RE: Conditional Formula to indicate Formula in cell Ron Coderre 01.08.2006 17:05:03 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: [Quoted Text] > 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) > > >

Re: Conditional Formula to indicate Formula in cell SteveW <sj_walton[ at ]nothotmail.com> 01.08.2006 17:36:40 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 <bob.NGs[ at ]xxxx.om> wrote= : [Quoted Text] > 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" <sj_walton[ at ]nothotmail.com> wrote in message > news:op.tdl33kuqevjsnp[ at ]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)

Re: Conditional Formula to indicate Formula in cell SteveW <sj_walton[ at ]nothotmail.com> 01.08.2006 17:43:15 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 = <ronREMOVETHIScoderre[ at ]bigfoot.com> wrote: [Quoted Text] > 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)

Re: Conditional Formula to indicate Formula in cell Ron Coderre 01.08.2006 18:05:01 Yes...you'd need to create it in each workbook that will use it. *********** Regards, Ron XL2002, WinXP "SteveW" wrote: [Quoted Text] > 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 > <ronREMOVETHIScoderre[ at ]bigfoot.com> 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) >

Re: Conditional Formula to indicate Formula in cell "Ragdyer" <RagDyer[ at ]cutoutmsn.com> 01.08.2006 23:18:09 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" <ronREMOVETHIScoderre[ at ]bigfoot.com> wrote in message news:AD060ADE-F175-4AE7-B5A7-EE88DBFA45AA[ at ]microsoft.com... [Quoted Text] > 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 >> <ronREMOVETHIScoderre[ at ]bigfoot.com> 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) >>

Re: Conditional Formula to indicate Formula in cell Ron Coderre 02.08.2006 00:12:01 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: [Quoted Text] > 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" <ronREMOVETHIScoderre[ at ]bigfoot.com> wrote in message > news:AD060ADE-F175-4AE7-B5A7-EE88DBFA45AA[ at ]microsoft.com... > > 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 > >> <ronREMOVETHIScoderre[ at ]bigfoot.com> 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) > >> > >