Formula or not?

Hi,

How can one determine if a cells contais a value or a formula? I'm trying to
use Conditional Formatting to color code the cells based on their content
(formula or value).

Does anyone know?

Thanks

-- 
J. Avelar


0
6/1/2005 8:50:17 PM
excel 39879 articles. 2 followers. Follow

11 Replies
679 Views

Similar Articles

[PageSpeed] 51

Here's a nifty macro that some kind folks in the group gave me some time
ago........it works super.
(watch out for email word-wrap)

Vaya con Dios,
Chuck, CABGx3


Public Sub IDFormulae()
Dim response As Variant
response = Application.InputBox("Identify Cells containing formulas with:" &
_
        vbNewLine & "1 - Red Border" & vbTab & _
vbTab & "5 - Blue Background" & _
        vbNewLine & "2 - Blue Border" & vbTab & _
vbTab & "6 - Green Background" & _
        vbNewLine & "3 - Green Border" & vbTab & _
vbTab & "7 - Clear Border Color" & _
        vbNewLine & "4 - Yellow Background" & _
vbTab & "8 - Clear Background Color", _
        Title:="Format Formulas as follows:", Type:=1)

If response = False Then Exit Sub
On Error Resume Next
With ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
    Select Case response
       Case 1
            .Borders.ColorIndex = 3 'sets Borders Red
            .Borders.Weight = xlThick 'makes Borders thick
       Case 2
            .Borders.ColorIndex = 5 'sets Borders Blue
            .Borders.Weight = xlThick 'makes Borders thick
       Case 3
            .Borders.ColorIndex = 4 'sets Borders green
            .Borders.Weight = xlThick 'makes Borders thick
       Case 4
            .Interior.ColorIndex = 36 'sets background yellow
       Case 5
            .Interior.ColorIndex = 34 'sets Background lite blue
       Case 6
            .Interior.ColorIndex = 35 'sets Background lite green
       Case 7
            .Borders.ColorIndex = xlColorIndexNone 'clears Borders set by
above
       Case 8
            .Interior.ColorIndex = xlColorIndexNone 'clears Backgrounds set
by above
       Case Else
           MsgBox "Not a valid option"
' Note that clearing Borders or Backgrounds in Cases 7 & 8
' only clears those which were set by this macro and does not
' affect other borders or backgrounds elsewhere on the sheet
    End Select
End With
On Error GoTo 0
End Sub






"J. Avelar" <joao.avelar@ig.com.br> wrote in message
news:#NfvfsuZFHA.796@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> How can one determine if a cells contais a value or a formula? I'm trying
to
> use Conditional Formatting to color code the cells based on their content
> (formula or value).
>
> Does anyone know?
>
> Thanks
>
> --
> J. Avelar
>
>


0
croberts (1377)
6/1/2005 8:58:43 PM
Thanks, Chuck. By your answer I understand that it cannot be done without
VBA, right?


"CLR" <croberts@tampabay.rr.com> escreveu na mensagem
news:O8gUxyuZFHA.2496@TK2MSFTNGP14.phx.gbl...
> Here's a nifty macro that some kind folks in the group gave me some time
> ago........it works super.
> (watch out for email word-wrap)
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> Public Sub IDFormulae()
> Dim response As Variant
> response = Application.InputBox("Identify Cells containing formulas with:"
&
> _
>         vbNewLine & "1 - Red Border" & vbTab & _
> vbTab & "5 - Blue Background" & _
>         vbNewLine & "2 - Blue Border" & vbTab & _
> vbTab & "6 - Green Background" & _
>         vbNewLine & "3 - Green Border" & vbTab & _
> vbTab & "7 - Clear Border Color" & _
>         vbNewLine & "4 - Yellow Background" & _
> vbTab & "8 - Clear Background Color", _
>         Title:="Format Formulas as follows:", Type:=1)
>
> If response = False Then Exit Sub
> On Error Resume Next
> With ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
>     Select Case response
>        Case 1
>             .Borders.ColorIndex = 3 'sets Borders Red
>             .Borders.Weight = xlThick 'makes Borders thick
>        Case 2
>             .Borders.ColorIndex = 5 'sets Borders Blue
>             .Borders.Weight = xlThick 'makes Borders thick
>        Case 3
>             .Borders.ColorIndex = 4 'sets Borders green
>             .Borders.Weight = xlThick 'makes Borders thick
>        Case 4
>             .Interior.ColorIndex = 36 'sets background yellow
>        Case 5
>             .Interior.ColorIndex = 34 'sets Background lite blue
>        Case 6
>             .Interior.ColorIndex = 35 'sets Background lite green
>        Case 7
>             .Borders.ColorIndex = xlColorIndexNone 'clears Borders set by
> above
>        Case 8
>             .Interior.ColorIndex = xlColorIndexNone 'clears Backgrounds
set
> by above
>        Case Else
>            MsgBox "Not a valid option"
> ' Note that clearing Borders or Backgrounds in Cases 7 & 8
> ' only clears those which were set by this macro and does not
> ' affect other borders or backgrounds elsewhere on the sheet
>     End Select
> End With
> On Error GoTo 0
> End Sub
>
>
>
>
>
>
> "J. Avelar" <joao.avelar@ig.com.br> wrote in message
> news:#NfvfsuZFHA.796@TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > How can one determine if a cells contais a value or a formula? I'm
trying
> to
> > use Conditional Formatting to color code the cells based on their
content
> > (formula or value).
> >
> > Does anyone know?
> >
> > Thanks
> >
> > --
> > J. Avelar
> >
> >
>
>


0
6/1/2005 9:08:42 PM
I dunno, never really had occasion to think about it.  This macro works so
fine, I just keep it in my Personal.xls file and use it when I need it, or
if I just want to see where the formulas are.......do F5 > Special >
Formulas  and that will highlight all the formulas in the sheet........

Vaya con Dios,
Chuck, CABGx3



"J. Avelar" <joao.avelar@ig.com.br> wrote in message
news:OjC4x2uZFHA.2884@tk2msftngp13.phx.gbl...
> Thanks, Chuck. By your answer I understand that it cannot be done without
> VBA, right?
>
>
> "CLR" <croberts@tampabay.rr.com> escreveu na mensagem
> news:O8gUxyuZFHA.2496@TK2MSFTNGP14.phx.gbl...
> > Here's a nifty macro that some kind folks in the group gave me some time
> > ago........it works super.
> > (watch out for email word-wrap)
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > Public Sub IDFormulae()
> > Dim response As Variant
> > response = Application.InputBox("Identify Cells containing formulas
with:"
> &
> > _
> >         vbNewLine & "1 - Red Border" & vbTab & _
> > vbTab & "5 - Blue Background" & _
> >         vbNewLine & "2 - Blue Border" & vbTab & _
> > vbTab & "6 - Green Background" & _
> >         vbNewLine & "3 - Green Border" & vbTab & _
> > vbTab & "7 - Clear Border Color" & _
> >         vbNewLine & "4 - Yellow Background" & _
> > vbTab & "8 - Clear Background Color", _
> >         Title:="Format Formulas as follows:", Type:=1)
> >
> > If response = False Then Exit Sub
> > On Error Resume Next
> > With ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
> >     Select Case response
> >        Case 1
> >             .Borders.ColorIndex = 3 'sets Borders Red
> >             .Borders.Weight = xlThick 'makes Borders thick
> >        Case 2
> >             .Borders.ColorIndex = 5 'sets Borders Blue
> >             .Borders.Weight = xlThick 'makes Borders thick
> >        Case 3
> >             .Borders.ColorIndex = 4 'sets Borders green
> >             .Borders.Weight = xlThick 'makes Borders thick
> >        Case 4
> >             .Interior.ColorIndex = 36 'sets background yellow
> >        Case 5
> >             .Interior.ColorIndex = 34 'sets Background lite blue
> >        Case 6
> >             .Interior.ColorIndex = 35 'sets Background lite green
> >        Case 7
> >             .Borders.ColorIndex = xlColorIndexNone 'clears Borders set
by
> > above
> >        Case 8
> >             .Interior.ColorIndex = xlColorIndexNone 'clears Backgrounds
> set
> > by above
> >        Case Else
> >            MsgBox "Not a valid option"
> > ' Note that clearing Borders or Backgrounds in Cases 7 & 8
> > ' only clears those which were set by this macro and does not
> > ' affect other borders or backgrounds elsewhere on the sheet
> >     End Select
> > End With
> > On Error GoTo 0
> > End Sub
> >
> >
> >
> >
> >
> >
> > "J. Avelar" <joao.avelar@ig.com.br> wrote in message
> > news:#NfvfsuZFHA.796@TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > How can one determine if a cells contais a value or a formula? I'm
> trying
> > to
> > > use Conditional Formatting to color code the cells based on their
> content
> > > (formula or value).
> > >
> > > Does anyone know?
> > >
> > > Thanks
> > >
> > > --
> > > J. Avelar
> > >
> > >
> >
> >
>
>


0
croberts (1377)
6/1/2005 9:19:58 PM
AFAIK a formula can not detect a formula. VBA it is.

Formulas and conditional formatting recalculate every time. You may not need
that. If you need to color formulas, maybe on the separate cell entry or at
the click of a button is often enough ?

Also; menu Edit > Goto > Special, check Formulas, OK. Now click an ugly
color in the Format toolbar and they're colored.

HTH. Best wishes Harald

"J. Avelar" <joao.avelar@ig.com.br> skrev i melding
news:OjC4x2uZFHA.2884@tk2msftngp13.phx.gbl...
> Thanks, Chuck. By your answer I understand that it cannot be done without
> VBA, right?

> > > How can one determine if a cells contais a value or a formula? I'm
> trying
> > to
> > > use Conditional Formatting to color code the cells based on their
> content
> > > (formula or value).
> > >
> > > Does anyone know?
> > >
> > > Thanks
> > >
> > > --
> > > J. Avelar
> > >
> > >
> >
> >
>
>


0
innocent (844)
6/1/2005 9:23:21 PM
Hi,

Try this:

1) go to menu Insert>Name>Define...
2) write a name - say HasFormula
3) input the following formula:
=GET.CELL(48,INDIRECT("RC",0))+0*now()
4) press Add, OK
5) select the range to apply conditional formatting to
6) go to menu Format>Conditional Formatting...
7) choose Formula is... in the first dropdown box
8) write the following formula: =HasFormula

Note: although I have personally never had issues with XML functions, some 
people report that they may occasionally cause Excel shutdown and loss of 
unsaved data. So you use it at your own risk.

Also, Jan-Karel Pieterse has a very neat file full of named XML formulas 
examples here: http://www.jkp-ads.com/Articles/ExcelNames.htm

Regards,
KL


"Harald Staff" <innocent@enron.invalid> wrote in message 
news:%23KRakAvZFHA.2996@TK2MSFTNGP10.phx.gbl...
> AFAIK a formula can not detect a formula. VBA it is.
>
> Formulas and conditional formatting recalculate every time. You may not 
> need
> that. If you need to color formulas, maybe on the separate cell entry or 
> at
> the click of a button is often enough ?
>
> Also; menu Edit > Goto > Special, check Formulas, OK. Now click an ugly
> color in the Format toolbar and they're colored.
>
> HTH. Best wishes Harald
>
> "J. Avelar" <joao.avelar@ig.com.br> skrev i melding
> news:OjC4x2uZFHA.2884@tk2msftngp13.phx.gbl...
>> Thanks, Chuck. By your answer I understand that it cannot be done without
>> VBA, right?
>
>> > > How can one determine if a cells contais a value or a formula? I'm
>> trying
>> > to
>> > > use Conditional Formatting to color code the cells based on their
>> content
>> > > (formula or value).
>> > >
>> > > Does anyone know?
>> > >
>> > > Thanks
>> > >
>> > > --
>> > > J. Avelar
>> > >
>> > >
>> >
>> >
>>
>>
>
> 


0
6/1/2005 9:38:41 PM
Thanks, but I'm afraid this works only for Excel 2003+. I'm still a 2000
guy...


"KL" <NOSPAMlapink2000@PLEASEhotmail.com> escreveu na mensagem
news:ewGqSKvZFHA.2796@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Try this:
>
> 1) go to menu Insert>Name>Define...
> 2) write a name - say HasFormula
> 3) input the following formula:
> =GET.CELL(48,INDIRECT("RC",0))+0*now()
> 4) press Add, OK
> 5) select the range to apply conditional formatting to
> 6) go to menu Format>Conditional Formatting...
> 7) choose Formula is... in the first dropdown box
> 8) write the following formula: =HasFormula
>
> Note: although I have personally never had issues with XML functions, some
> people report that they may occasionally cause Excel shutdown and loss of
> unsaved data. So you use it at your own risk.
>
> Also, Jan-Karel Pieterse has a very neat file full of named XML formulas
> examples here: http://www.jkp-ads.com/Articles/ExcelNames.htm
>
> Regards,
> KL
>
>
> "Harald Staff" <innocent@enron.invalid> wrote in message
> news:%23KRakAvZFHA.2996@TK2MSFTNGP10.phx.gbl...
> > AFAIK a formula can not detect a formula. VBA it is.
> >
> > Formulas and conditional formatting recalculate every time. You may not
> > need
> > that. If you need to color formulas, maybe on the separate cell entry or
> > at
> > the click of a button is often enough ?
> >
> > Also; menu Edit > Goto > Special, check Formulas, OK. Now click an ugly
> > color in the Format toolbar and they're colored.
> >
> > HTH. Best wishes Harald
> >
> > "J. Avelar" <joao.avelar@ig.com.br> skrev i melding
> > news:OjC4x2uZFHA.2884@tk2msftngp13.phx.gbl...
> >> Thanks, Chuck. By your answer I understand that it cannot be done
without
> >> VBA, right?
> >
> >> > > How can one determine if a cells contais a value or a formula? I'm
> >> trying
> >> > to
> >> > > use Conditional Formatting to color code the cells based on their
> >> content
> >> > > (formula or value).
> >> > >
> >> > > Does anyone know?
> >> > >
> >> > > Thanks
> >> > >
> >> > > --
> >> > > J. Avelar
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
>


0
6/2/2005 12:34:13 PM
I don't have xl2k anymore, but I'd try it one more time if I were you.

"J. Avelar" wrote:
> 
> Thanks, but I'm afraid this works only for Excel 2003+. I'm still a 2000
> guy...
> 
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> escreveu na mensagem
> news:ewGqSKvZFHA.2796@TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Try this:
> >
> > 1) go to menu Insert>Name>Define...
> > 2) write a name - say HasFormula
> > 3) input the following formula:
> > =GET.CELL(48,INDIRECT("RC",0))+0*now()
> > 4) press Add, OK
> > 5) select the range to apply conditional formatting to
> > 6) go to menu Format>Conditional Formatting...
> > 7) choose Formula is... in the first dropdown box
> > 8) write the following formula: =HasFormula
> >
> > Note: although I have personally never had issues with XML functions, some
> > people report that they may occasionally cause Excel shutdown and loss of
> > unsaved data. So you use it at your own risk.
> >
> > Also, Jan-Karel Pieterse has a very neat file full of named XML formulas
> > examples here: http://www.jkp-ads.com/Articles/ExcelNames.htm
> >
> > Regards,
> > KL
> >
> >
> > "Harald Staff" <innocent@enron.invalid> wrote in message
> > news:%23KRakAvZFHA.2996@TK2MSFTNGP10.phx.gbl...
> > > AFAIK a formula can not detect a formula. VBA it is.
> > >
> > > Formulas and conditional formatting recalculate every time. You may not
> > > need
> > > that. If you need to color formulas, maybe on the separate cell entry or
> > > at
> > > the click of a button is often enough ?
> > >
> > > Also; menu Edit > Goto > Special, check Formulas, OK. Now click an ugly
> > > color in the Format toolbar and they're colored.
> > >
> > > HTH. Best wishes Harald
> > >
> > > "J. Avelar" <joao.avelar@ig.com.br> skrev i melding
> > > news:OjC4x2uZFHA.2884@tk2msftngp13.phx.gbl...
> > >> Thanks, Chuck. By your answer I understand that it cannot be done
> without
> > >> VBA, right?
> > >
> > >> > > How can one determine if a cells contais a value or a formula? I'm
> > >> trying
> > >> > to
> > >> > > use Conditional Formatting to color code the cells based on their
> > >> content
> > >> > > (formula or value).
> > >> > >
> > >> > > Does anyone know?
> > >> > >
> > >> > > Thanks
> > >> > >
> > >> > > --
> > >> > > J. Avelar
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >

-- 

Dave Peterson
0
ec357201 (5290)
6/2/2005 1:43:16 PM
Wow! What a surprise! I never had a version higher than 2K and it works 
great on my XL97 and XL2K. If you use a non-English version of the office 
then that is a different issue as both the functions and the "RC" may need 
to be translated to your system's language.

Regards,
KL

"J. Avelar" <joao.avelar@ig.com.br> wrote in message 
news:Oo%23W%2372ZFHA.2996@TK2MSFTNGP10.phx.gbl...
> Thanks, but I'm afraid this works only for Excel 2003+. I'm still a 2000
> guy...
>
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> escreveu na mensagem
> news:ewGqSKvZFHA.2796@TK2MSFTNGP10.phx.gbl...
>> Hi,
>>
>> Try this:
>>
>> 1) go to menu Insert>Name>Define...
>> 2) write a name - say HasFormula
>> 3) input the following formula:
>> =GET.CELL(48,INDIRECT("RC",0))+0*now()
>> 4) press Add, OK
>> 5) select the range to apply conditional formatting to
>> 6) go to menu Format>Conditional Formatting...
>> 7) choose Formula is... in the first dropdown box
>> 8) write the following formula: =HasFormula
>>
>> Note: although I have personally never had issues with XML functions, 
>> some
>> people report that they may occasionally cause Excel shutdown and loss of
>> unsaved data. So you use it at your own risk.
>>
>> Also, Jan-Karel Pieterse has a very neat file full of named XML formulas
>> examples here: http://www.jkp-ads.com/Articles/ExcelNames.htm
>>
>> Regards,
>> KL
>>
>>
>> "Harald Staff" <innocent@enron.invalid> wrote in message
>> news:%23KRakAvZFHA.2996@TK2MSFTNGP10.phx.gbl...
>> > AFAIK a formula can not detect a formula. VBA it is.
>> >
>> > Formulas and conditional formatting recalculate every time. You may not
>> > need
>> > that. If you need to color formulas, maybe on the separate cell entry 
>> > or
>> > at
>> > the click of a button is often enough ?
>> >
>> > Also; menu Edit > Goto > Special, check Formulas, OK. Now click an ugly
>> > color in the Format toolbar and they're colored.
>> >
>> > HTH. Best wishes Harald
>> >
>> > "J. Avelar" <joao.avelar@ig.com.br> skrev i melding
>> > news:OjC4x2uZFHA.2884@tk2msftngp13.phx.gbl...
>> >> Thanks, Chuck. By your answer I understand that it cannot be done
> without
>> >> VBA, right?
>> >
>> >> > > How can one determine if a cells contais a value or a formula? I'm
>> >> trying
>> >> > to
>> >> > > use Conditional Formatting to color code the cells based on their
>> >> content
>> >> > > (formula or value).
>> >> > >
>> >> > > Does anyone know?
>> >> > >
>> >> > > Thanks
>> >> > >
>> >> > > --
>> >> > > J. Avelar
>> >> > >
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
> 


0
6/2/2005 2:09:35 PM
Very good response.  I bet it's the international issues.

KL wrote:
> 
> Wow! What a surprise! I never had a version higher than 2K and it works
> great on my XL97 and XL2K. If you use a non-English version of the office
> then that is a different issue as both the functions and the "RC" may need
> to be translated to your system's language.
> 
> Regards,
> KL
<<snipped>>
0
ec357201 (5290)
6/2/2005 2:17:15 PM
Hi,

I am not sure, but if you are using a Portuguese version of Office you may 
want to try:

=OBTER.CEL(48;INDIRECTO("LC";0))+0*HOJE()

Regards,
KL

"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message 
news:egtFA03ZFHA.3488@tk2msftngp13.phx.gbl...
> Wow! What a surprise! I never had a version higher than 2K and it works 
> great on my XL97 and XL2K. If you use a non-English version of the office 
> then that is a different issue as both the functions and the "RC" may need 
> to be translated to your system's language.
>
> Regards,
> KL
>
> "J. Avelar" <joao.avelar@ig.com.br> wrote in message 
> news:Oo%23W%2372ZFHA.2996@TK2MSFTNGP10.phx.gbl...
>> Thanks, but I'm afraid this works only for Excel 2003+. I'm still a 2000
>> guy...
>>
>>
>> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> escreveu na mensagem
>> news:ewGqSKvZFHA.2796@TK2MSFTNGP10.phx.gbl...
>>> Hi,
>>>
>>> Try this:
>>>
>>> 1) go to menu Insert>Name>Define...
>>> 2) write a name - say HasFormula
>>> 3) input the following formula:
>>> =GET.CELL(48,INDIRECT("RC",0))+0*now()
>>> 4) press Add, OK
>>> 5) select the range to apply conditional formatting to
>>> 6) go to menu Format>Conditional Formatting...
>>> 7) choose Formula is... in the first dropdown box
>>> 8) write the following formula: =HasFormula
>>>
>>> Note: although I have personally never had issues with XML functions, 
>>> some
>>> people report that they may occasionally cause Excel shutdown and loss 
>>> of
>>> unsaved data. So you use it at your own risk.
>>>
>>> Also, Jan-Karel Pieterse has a very neat file full of named XML formulas
>>> examples here: http://www.jkp-ads.com/Articles/ExcelNames.htm
>>>
>>> Regards,
>>> KL
>>>
>>>
>>> "Harald Staff" <innocent@enron.invalid> wrote in message
>>> news:%23KRakAvZFHA.2996@TK2MSFTNGP10.phx.gbl...
>>> > AFAIK a formula can not detect a formula. VBA it is.
>>> >
>>> > Formulas and conditional formatting recalculate every time. You may 
>>> > not
>>> > need
>>> > that. If you need to color formulas, maybe on the separate cell entry 
>>> > or
>>> > at
>>> > the click of a button is often enough ?
>>> >
>>> > Also; menu Edit > Goto > Special, check Formulas, OK. Now click an 
>>> > ugly
>>> > color in the Format toolbar and they're colored.
>>> >
>>> > HTH. Best wishes Harald
>>> >
>>> > "J. Avelar" <joao.avelar@ig.com.br> skrev i melding
>>> > news:OjC4x2uZFHA.2884@tk2msftngp13.phx.gbl...
>>> >> Thanks, Chuck. By your answer I understand that it cannot be done
>> without
>>> >> VBA, right?
>>> >
>>> >> > > How can one determine if a cells contais a value or a formula? 
>>> >> > > I'm
>>> >> trying
>>> >> > to
>>> >> > > use Conditional Formatting to color code the cells based on their
>>> >> content
>>> >> > > (formula or value).
>>> >> > >
>>> >> > > Does anyone know?
>>> >> > >
>>> >> > > Thanks
>>> >> > >
>>> >> > > --
>>> >> > > J. Avelar
>>> >> > >
>>> >> > >
>>> >> >
>>> >> >
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
> 


0
6/2/2005 2:21:03 PM
Thanks again. i'll try to "translate" the formula, and see what happens.


"KL" <NOSPAMlapink2000@PLEASEhotmail.com> escreveu na mensagem
news:OErPa63ZFHA.2768@tk2msftngp13.phx.gbl...
> Hi,
>
> I am not sure, but if you are using a Portuguese version of Office you may
> want to try:
>
> =OBTER.CEL(48;INDIRECTO("LC";0))+0*HOJE()
>
> Regards,
> KL
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:egtFA03ZFHA.3488@tk2msftngp13.phx.gbl...
> > Wow! What a surprise! I never had a version higher than 2K and it works
> > great on my XL97 and XL2K. If you use a non-English version of the
office
> > then that is a different issue as both the functions and the "RC" may
need
> > to be translated to your system's language.
> >
> > Regards,
> > KL
> >
> > "J. Avelar" <joao.avelar@ig.com.br> wrote in message
> > news:Oo%23W%2372ZFHA.2996@TK2MSFTNGP10.phx.gbl...
> >> Thanks, but I'm afraid this works only for Excel 2003+. I'm still a
2000
> >> guy...
> >>
> >>
> >> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> escreveu na mensagem
> >> news:ewGqSKvZFHA.2796@TK2MSFTNGP10.phx.gbl...
> >>> Hi,
> >>>
> >>> Try this:
> >>>
> >>> 1) go to menu Insert>Name>Define...
> >>> 2) write a name - say HasFormula
> >>> 3) input the following formula:
> >>> =GET.CELL(48,INDIRECT("RC",0))+0*now()
> >>> 4) press Add, OK
> >>> 5) select the range to apply conditional formatting to
> >>> 6) go to menu Format>Conditional Formatting...
> >>> 7) choose Formula is... in the first dropdown box
> >>> 8) write the following formula: =HasFormula
> >>>
> >>> Note: although I have personally never had issues with XML functions,
> >>> some
> >>> people report that they may occasionally cause Excel shutdown and loss
> >>> of
> >>> unsaved data. So you use it at your own risk.
> >>>
> >>> Also, Jan-Karel Pieterse has a very neat file full of named XML
formulas
> >>> examples here: http://www.jkp-ads.com/Articles/ExcelNames.htm
> >>>
> >>> Regards,
> >>> KL
> >>>
> >>>
> >>> "Harald Staff" <innocent@enron.invalid> wrote in message
> >>> news:%23KRakAvZFHA.2996@TK2MSFTNGP10.phx.gbl...
> >>> > AFAIK a formula can not detect a formula. VBA it is.
> >>> >
> >>> > Formulas and conditional formatting recalculate every time. You may
> >>> > not
> >>> > need
> >>> > that. If you need to color formulas, maybe on the separate cell
entry
> >>> > or
> >>> > at
> >>> > the click of a button is often enough ?
> >>> >
> >>> > Also; menu Edit > Goto > Special, check Formulas, OK. Now click an
> >>> > ugly
> >>> > color in the Format toolbar and they're colored.
> >>> >
> >>> > HTH. Best wishes Harald
> >>> >
> >>> > "J. Avelar" <joao.avelar@ig.com.br> skrev i melding
> >>> > news:OjC4x2uZFHA.2884@tk2msftngp13.phx.gbl...
> >>> >> Thanks, Chuck. By your answer I understand that it cannot be done
> >> without
> >>> >> VBA, right?
> >>> >
> >>> >> > > How can one determine if a cells contais a value or a formula?
> >>> >> > > I'm
> >>> >> trying
> >>> >> > to
> >>> >> > > use Conditional Formatting to color code the cells based on
their
> >>> >> content
> >>> >> > > (formula or value).
> >>> >> > >
> >>> >> > > Does anyone know?
> >>> >> > >
> >>> >> > > Thanks
> >>> >> > >
> >>> >> > > --
> >>> >> > > J. Avelar
> >>> >> > >
> >>> >> > >
> >>> >> >
> >>> >> >
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>


0
6/2/2005 10:37:09 PM
Reply:

Similar Artilces:

coping formula #2
I am trying to copy a formula down a column. When I copy the formula down I want the cell to be blank until i fill out the cells to give me my balance. My three columns are Debit / credit / balance. My formual reads : f7(balance column)+e8(credit column)-d8 (debit column) when i copy the formula down form my balance column it gives me the balance in every cell. I would like for it to be blank until i fill in my debits and credits. I hope you understand the way i wrote this thanks much -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200803/1 =IF(...

printing sheet formulas
I need to print an Excel application which consists of many files that contain formulas, macros and add-ins. First, I want to print all formulas contained in a file (not the contents of the cells but the formulas) so that I can have a "blueprint" of the application as it is referencing multiple sheets and column names etc. I want to do this one file at a time. Then I want to examine the contents of the add-in that was created by the company many years ago aka not a Microsoft addin. Where can I go to see the contents and what the addin is doing? Everywhere I have been shows noth...

Nested Formula Giving Incorrect Answer
Hi, I am looking at an issue one of our staff has. He is using the below formula across a set of data =IF(INT(MOD(E13,C14))>1,INT(E13/C14),0) This returns 0, if E13 is not disvisible by C14, or the closest integer (rounded down) if it is. The formula works perfectly for most of the data, but when E13 is 50,000 it throws a wobbler and returns 0, it doesn't matter what the value of C14 is. After doing a bit of testing, and typing figures into the formula manually, I have figured out that if a number between 50,000 and 50,002 is entered, the formula returns 0, above or...

Formula to sum in columns that change weekly
I enter a column of data weekly into a spread sheet and then total each row for the last 4,8& 12 weeks. As the column numbers to be added change weekly I have to change the formula weekly. Is there a way to do this automatically? Assuming no blanks, this will total the last 4, 8 and 12 weeks in row 2: =SUM(OFFSET($A$2,0,COUNTA($2:$2)-4,1,4) =SUM(OFFSET($A$2,0,COUNTA($2:$2)-8,1,8) =SUM(OFFSET($A$2,0,COUNTA($2:$2)-12,1,12) In article <lf%Lb.3326$Z%4.1704@newssvr24.news.prodigy.com>, "Robert L. Salisbury" <rlsalisbury@c-air-s.com> wrote: > I e...

Replace a portion of a formula
Does anyone know of a way that I can replace a portion of a formula throughout a worksheet without changing the other part? I have been able to find the cells that need to be changed by searching for =SUM($C?*$E$1) I need to change the C to a D is all cells. Is this possible? I'd appreciate any help! Thanks! Find SUM($C and replace with SUM($D use the look in formula option. HTH, Bernie MS Excel MVP "krkward" <krkward@discussions.microsoft.com> wrote in message news:25D2BAD8-646C-4C93-AD21-01B54DBB396E@microsoft.com... > Does anyon...

Help with Formula logic
I have this formula in Sheet2 =IF(OR(ISTEXT(L2),ISBLANK(L2)),"",IF(OR($M2>'Sheet1'!$A2+1,ISBLANK($M2)),NETWORKDAYS('Sheet1!$A$2,$L2),"")) to store the data in the W column. And then I use disaply it in the sheet1 by using "=AVERAGE('Sheet2'!W3:W141)" I intend to combine two formula together so I do not need to create many columns in Sheet2 because the A colcumn in the Sheet1 increases. I tried to use SumIf and Sumproduct, however, I have a hard time to make it right. Any suggestion will be highly appreciated. I haven't attempted to c...

List separators ; becoming \ (only in formulas in names) when opening file on another PC
Hi, I've got a worksheet for analysing data's to be shown in XY scatters graphs. I used Tushar Mehta's excellent add-in to keep the scale limits updated all the time. The problem is: it works perfectly on my PC and does not work on other PC in my company: The names used to define the values giving the scale(s) contain formulas in which the list separators ";" have been replaced by a (funny?) "\" (yes, backslash!!!). I've checked the regional settings, list separators,... They are the same on the two other PCs I checked. Can anybody help ??? Thks in advance ...

macro to copy Vlookup formula to some cells with a filter on
Hello, I was wondering if somebody could help me with this macro… I have some data with a filter on, so that only the rows with blank cells are showing. I want to enter a VLOOKUP in the cell, and copy it down to all the blank cells. I tried just recording a macro, but when I run the macro it doesn’t work. Any ideas of how to do it? Example: 1) Before the filter: Colums A -B - C A x x - 1st Row A - 2nd Row A x x - 3rd Row A - 4th row A x x - 5th row 2) After the fil...

Excel formula translator
Very nice formula translator for MS-Excel. Translates into 16 languages (including Google Spreadsheet) http://excel.applewood.nl/forms/3000/FRM-004/?frmTaal=EN NL, EN, DE, DK, NO, HU, CZ, ES, IT, FR, NO, FI, BR, PT, PL, SE ...

Conditional format: if formula result is #N/A then make text color white? How?
I have a series of cells that I need to graph. In order to avoid false zero values, I've used an if formula, similar to: =if(isnumber(A2),A2,na()) now I'm going to be using this workbook with some non-computer people who are easily confused by stuff they don't understand, such as a bunch of cells with #N/A in them, so I want to use conditional formatting to set the text color in those cells to white (same as background) when the value shown in #N/A. Unfortunately, I can't figure out what I should put in the conditional formatting box to get this to work- cell value = ?? I'...

Formula help #3
Sheet1 has a name in A1 & a number in B1, name in A2 number in B2 and s on (numbers will continually be changed). On sheet2 I used =a or = and so on (the results of =a or =b are the names from sheet 1). Wha I want to accomplish is every time I type =a or =b and the name appear I also want the result from sheet1 B1 and so on to appear in the cel directly below. Not very experienced just learning. Looking fo help -- Message posted from http://www.ExcelForum.com Hi Ljsimm You post is a bit vague, but if you mean you want B1 on sheet 2 to match up with the name on Sheet 1. Try: =IF(...

Excel formula for this...
Ok i want an excel formula to do this hopefully simple task... Say i have cell A1 with the word "dog" in it and cell B2 with the word "Cat" in it i want cell H5 to have the data in the previously mentioned cells so that it reads "dog, Cat" So in english i guess i want multiple cell values in once cell separated by a space or a comma or whatever. Thanks Max Try: =A1&", "&B2 MRO "Max Sand" <Max@gk.com> wrote in message news:Yx55b.4433$bC1.2817@newsfep4-winn.server.ntli.net... > Ok i want an excel formula to do this hope...

Lookup Formula Question
I'm pretty new to excel and I'm wondering if anyone can help me. I want to create a formula so that if A1=1, then F2:F5 are entered into C2:C5, or if A1=2, then G2:G5 are entered into C2:C5, etc. for more values forA1. Ex: A B C D E F G 1 2 5 6 3 10 7 4 15 8 5 ...

Excel formula help needed!
Hello everyone, I would like to create a formula that would calculate a different levy charge dependent on the amount. The example is below and any help would be really appreciated. In circumstances where amounts owed do not exceed £100 or $100 12.5% charge will apply, 4% on the next £400 or $400, 2.5% on the next £1500 or $1500, 1% on the next £8000 or $8000 and 0.25% on any additional sum. In short different %'s will apply at different levels depending on monies mowed. I'm sure that there will be use of If/And but I'm not sure on the st...

How do I get the formula bar in excel 2003
I have Excel 2003 on two PC's - on one I have the row between the toolbars and the worksheet which shows the formula currently being worked on "the formula bar" as it is called if I left click on it. On the other PC this row is not there so I have to edit all formulae and entries in the cell in the work sheet. How do I get the "formula bar" please > Try this: <Alt> <V> <F> OR <View> and then click on "FormulaBar". -- HTH, RD --------------------------------------------------------------------------- Please keep all corre...

What is appropriate formula?
Hi, a very inexperienced Excel user needs any help I can get. I have supplier item data for yr 1 and yr 2. Year 2 has some price ammendments and some additional items. What formula can I use to identify any changes in year2 data? Many thanks in advance Kev You need to provide sufficient details if you want some help. How is your data laid out? Do you have separate sheets for year1 and year2? What columns do you use, and what data do you have in them? How many rows of data do you have? What do you want to end up with ultimately? etc. etc. Pete On Nov 13, 3:59 pm, KevB <K...@discus...

Format in if formula
I want to use the "if" function and change the format (color) of the cell if the outcome is true or false. Please help ...

Formula Question #8
Hi, I have a list of numbers seperated by spaces [A]: Next to SOME of those numbers are other numbers [B}. The second set of numbers on the right represent hours. All data between two spaces represents a different day. In a third column [C], at the bottom, I want to total the hours that are in the second column, so I know how many hours were worked for each day. (As in parenthesis in the example below.) Is there a way to do this without using a macro? I hope this was clear. Thanks, CC A B C 4 4 5 2 6 (6) 2 3 3 (3) 1 7 2 (7) 2 3 5 6 8 7 5 (10) H...

Using formula to create multiple versions of a Graphs
I would like to be able to use a formula in an Excel 2003 graph, to dynamically modify the source and lable data of a graph based on a cell reference. For Example: When I enter the number 1 into cell "a1" I would like to have the graph automatically change to show the map points related to report 1, when I enter 2 inot "A1", I would like the source data to change to show points related to report 2. How can this be done? On Thu, 26 Jul 2007, in microsoft.public.excel.charting, BarryL <BarryL@discussions.microsoft.com> said: >I would like to be able to use a...

Cube formulas and Excel compatibility
Hi I have an Excel xlsx file that has many cube formulas (connected to local cube built in Excel 2003). I had had to send the file to someone but before that I had to convert all cube formulas to static values and save the file as XLS (2003). In order to convert the values I used the following macro: Option Explicit Sub CubeFormulasToStaticValues() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim mwksSheetItem As Excel.Worksheet Dim mrngCell As Excel.Range Dim msCellValue As String, msSheetName As String On Error GoTo ErrHandler For Each mwksSheet...

average formula help #2
We have a range of cells A1:A10 which contain values as follows:- 1 2 4 5 0 0 6 8 9 10 We need Cell A11 to display an average of A1 to A10 but ignoring the 0 values. Is this possible with a formula? Thanks in advance Kevin Kevin, try this =AVERAGE(IF(A1:A10 <>0,A1:A10 )) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 97 & 2000 ** remove news from my email address to reply by email ** "kevin carter&quo...

Modify a formula
This is showing how much of a novice I am, but I'm learning. I need to change this formula so that it not only shows the message if multiples of 84 is not entered but if multiples of 61 is not entered into the same cell. Thanks. =IF(MOD(F21,84)<>0,"Qty in this Row is not in multiples of 84","") Hi! Try this: =IF(MOD(F21,84)=0,"",IF(MOD(F21,61)=0,"","Qty in this Row is not in multiples of 84 or 61")) Biff "Eintsein_mc2" <simonmerks@bigpond.com> wrote in message news:1127014192.905805.63830@o13g2000cwo.googlegrou...

Simplifying a formula that prevents negative numbers
Is there a shorter way of writing the following formula for calculating an alimony deduction? =IF(SUM(C11-C9)>=C8,C8,IF(SUM(C11-C9)>=0,SUM(C11-C9),0)) The formla calculates the alimony deductible (if any) on the 1040 return for a given amount of total payments for alimony and child support per year. The rules are: 1) Payments are first allocated to child support (which is not deductible) 2) Any remaining payment is allocated to alimony, subject to a maximum amount that is required in a decree of separation or divorce; that is, any overpayments of alimony are also not ...

Full page formulas
How do I do this for every row? Example: =SUM(C4*0.5) =SUM(E4*15) =SUM(G4+F4+D4) =SUM(I4-H4) The first question is what are you intending the SUM function to do in each of your formulae? In what way do you want =SUM(C4*0.5) to differ from =C4*0.5 ? In what way do you want =SUM(E4*15) to differ from =E4*15 ? In what way do you want =SUM(G4+F4+D4) to differ from =G4+F4+D4 ? In what way do you want =SUM(I4-H4) to differ from =I4-H4 ? If you don't understand what the SUM function does, try looking it up in Excel help. If you want to fill the formulae down a column, look at th...

countif formula #3
Would like to count the cells in a column that are filled with a color ( yellow in this case). The cells have other values for other formulas but want to count the color filled cells. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH RP (remove nothere from the email address if mailing direct) "B.White" <B.White @discussions.microsoft.com> wrote in message news:20FA6D52-13D5-45C3-A447-A329DFAF4832@microsoft.com... > Would like to count the cells in a column that are filled with a color ( > yellow in this case). The cells have other values for other...