conditional formating with isodd and iseven
 

I have consective numbers in column A. In column B is formula =isodd
which returns True or False, and this works for me but I have tried to
use =isodd and =iseven as a conditional format formula with no luck.
My question is, is it possible to use either of these arguments in a
conditional format formula. I have tried severial syntax with no luck.
I most often get error message "May not use referance to other sheet
or book...." I know you guys can crack this.

Regards

BigD

conditional formating with isodd and iseven
 

I most often get error message "May not use referance to other sheet or
book...."

That's because those functions are part of the Analysis ToolPak add-in which
is another file (workbook).

For even numbers:

=MOD(A1,2)=0

For odd numbers:

=MOD(A2,2)0

--
Biff
Microsoft Excel MVP


"BIG D" wrote in message
...
I have consective numbers in column A. In column B is formula =isodd
which returns True or False, and this works for me but I have tried to
use =isodd and =iseven as a conditional format formula with no luck.
My question is, is it possible to use either of these arguments in a
conditional format formula. I have tried severial syntax with no luck.
I most often get error message "May not use referance to other sheet
or book...." I know you guys can crack this.

Regards

BigD

conditional formating with isodd and iseven
 

I don't know the formula isodd, but you can use this formula:
=MOD(A1,2)=1
which gives you false for any even and a true for any odd number.

hth

Carlo

On Nov 20, 2:12 pm, BIG D wrote:
I have consective numbers in column A. In column B is formula =isodd
which returns True or False, and this works for me but I have tried to
use =isodd and =iseven as a conditional format formula with no luck.
My question is, is it possible to use either of these arguments in a
conditional format formula. I have tried severial syntax with no luck.
I most often get error message "May not use referance to other sheet
or book...." I know you guys can crack this.

Regards

BigD

conditional formating with isodd and iseven
 

Do you need the =1, Carlo? Can't you just use =MOD(A1,2) ?
--
David Biddulph

"carlo" wrote in message
...
I don't know the formula isodd, but you can use this formula:
=MOD(A1,2)=1
which gives you false for any even and a true for any odd number.

hth

Carlo

On Nov 20, 2:12 pm, BIG D wrote:
I have consective numbers in column A. In column B is formula =isodd
which returns True or False, and this works for me but I have tried to
use =isodd and =iseven as a conditional format formula with no luck.
My question is, is it possible to use either of these arguments in a
conditional format formula. I have tried severial syntax with no luck.
I most often get error message "May not use referance to other sheet
or book...." I know you guys can crack this.

Regards

BigD

conditional formating with isodd and iseven
 

Can't you just use =MOD(A1,2) ?

Yes, you could. I included the 0 in my reply just to show some uniformity
with the even numbers formula.

=MOD(A1,2)=0
=MOD(A1,2)0


--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Do you need the =1, Carlo? Can't you just use =MOD(A1,2) ?
--
David Biddulph

"carlo" wrote in message
...
I don't know the formula isodd, but you can use this formula:
=MOD(A1,2)=1
which gives you false for any even and a true for any odd number.

hth

Carlo

On Nov 20, 2:12 pm, BIG D wrote:
I have consective numbers in column A. In column B is formula =isodd
which returns True or False, and this works for me but I have tried to
use =isodd and =iseven as a conditional format formula with no luck.
My question is, is it possible to use either of these arguments in a
conditional format formula. I have tried severial syntax with no luck.
I most often get error message "May not use referance to other sheet
or book...." I know you guys can crack this.

Regards

BigD

conditional formating with isodd and iseven
 

On Nov 20, 12:56 pm, "T. Valko" wrote:
Can't you just use =MOD(A1,2) ?

Yes, you could. I included the 0 in my reply just to show some uniformity
with the even numbers formula.

=MOD(A1,2)=0
=MOD(A1,2)0

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk wrote in ...



Do you need the =1, Carlo? Can't you just use =MOD(A1,2) ?
--
David Biddulph

"carlo" wrote in message
...
I don't know the formula isodd, but you can use this formula:
=MOD(A1,2)=1
which gives you false for any even and a true for any odd number.

hth

Carlo

On Nov 20, 2:12 pm, BIG D wrote:
I have consective numbers in column A. In column B is formula =isodd
which returns True or False, and this works for me but I have tried to
use =isodd and =iseven as a conditional format formula with no luck.
My question is, is it possible to use either of these arguments in a
conditional format formula. I have tried severial syntax with no luck.
I most often get error message "May not use referance to other sheet
or book...." I know you guys can crack this.

Regards

BigD- Hide quoted text -

- Show quoted text -

Thanks everyone! The formulas work and solve the problem nicely.
In response to Valko I do have the analysis tool pack installed on all
of my machines which makes me wonder if the isodd command is for some
reason not available in conditional formating. I do not know enough
about excel to test the command in cf but I am researching it.

Regards

BigD

conditional formating with isodd and iseven
 

Since ATP is an add-in it exists physically on another sheet and you cannot
refer to other sheets when doing CF unless you use a defined name. You can
bypass ATP by putting ISODD in another cell hidden from view, or even better
in another sheet, for instance if the cell you want to CF is in Sheet1

=ISODD(Sheet1!A1)

if you put that in another sheet and call that cell for ODD
(insertnamedefine)

then you can select A1 in Sheet1 and use

=ODD=TRUE

and the CF will work

now you can hide this particular sheet with the isodd formula and nobody
will have a clue


--


Regards,


Peo Sjoblom


"BIG D" wrote in message
...
On Nov 20, 12:56 pm, "T. Valko" wrote:
Can't you just use =MOD(A1,2) ?

Yes, you could. I included the 0 in my reply just to show some
uniformity
with the even numbers formula.

=MOD(A1,2)=0
=MOD(A1,2)0

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk wrote in
...



Do you need the =1, Carlo? Can't you just use =MOD(A1,2) ?
--
David Biddulph

"carlo" wrote in message
...
I don't know the formula isodd, but you can use this formula:
=MOD(A1,2)=1
which gives you false for any even and a true for any odd number.

hth

Carlo

On Nov 20, 2:12 pm, BIG D wrote:
I have consective numbers in column A. In column B is formula =isodd
which returns True or False, and this works for me but I have tried
to
use =isodd and =iseven as a conditional format formula with no luck.
My question is, is it possible to use either of these arguments in a
conditional format formula. I have tried severial syntax with no
luck.
I most often get error message "May not use referance to other sheet
or book...." I know you guys can crack this.

Regards

BigD- Hide quoted text -

- Show quoted text -

Thanks everyone! The formulas work and solve the problem nicely.
In response to Valko I do have the analysis tool pack installed on all
of my machines which makes me wonder if the isodd command is for some
reason not available in conditional formating. I do not know enough
about excel to test the command in cf but I am researching it.

Regards

BigD

conditional formating with isodd and iseven
 

A question here for the experts.

I have an add-in that I reference in a workbook using VBE and ToolsReferences.

UDF's from this add-in can then be used in a CF formula.

If I reference FUNCRES.xla I still cannot use the ATP functions in CF formulas.

I assume the add-ins installed with Excel act differently from a regular
user-built add-in.

Correct?


Gord Dibben MS Excel MVP


On Tue, 20 Nov 2007 15:04:01 -0800, "Peo Sjoblom" wrote:

Since ATP is an add-in it exists physically on another sheet and you cannot
refer to other sheets when doing CF unless you use a defined name. You can
bypass ATP by putting ISODD in another cell hidden from view, or even better
in another sheet, for instance if the cell you want to CF is in Sheet1

=ISODD(Sheet1!A1)

if you put that in another sheet and call that cell for ODD
(insertnamedefine)

then you can select A1 in Sheet1 and use

=ODD=TRUE

and the CF will work

now you can hide this particular sheet with the isodd formula and nobody
will have a clue

conditional formating with isodd and iseven
 

Gord,

you ARE one of the experts !! <vbg

Pete

On Nov 21, 12:02 am, Gord Dibben <gorddibbATshawDOTca wrote:
A question here for the experts.

I have an add-in that I reference in a workbook using VBE and ToolsReferences.

UDF's from this add-in can then be used in a CF formula.

If I reference FUNCRES.xla I still cannot use the ATP functions in CF formulas.

I assume the add-ins installed with Excel act differently from a regular
user-built add-in.

Correct?

Gord Dibben MS Excel MVP



On Tue, 20 Nov 2007 15:04:01 -0800, "Peo Sjoblom" wrote:
Since ATP is an add-in it exists physically on another sheet and you cannot
refer to other sheets when doing CF unless you use a defined name. You can
bypass ATP by putting ISODD in another cell hidden from view, or even better
in another sheet, for instance if the cell you want to CF is in Sheet1

=ISODD(Sheet1!A1)

if you put that in another sheet and call that cell for ODD
(insertnamedefine)

then you can select A1 in Sheet1 and use

=ODD=TRUE

and the CF will work

now you can hide this particular sheet with the isodd formula and nobody
will have a clue- Hide quoted text -

- Show quoted text -

conditional formating with isodd and iseven
 

Pete

I may be an MVP but would not consider myself an expert in Excel.

More like an intermediate user.

Thanks for the vote of confidence, however<g


Gord

On Tue, 20 Nov 2007 16:42:41 -0800 (PST), Pete_UK wrote:

Gord,

you ARE one of the experts !! <vbg

Pete

On Nov 21, 12:02 am, Gord Dibben <gorddibbATshawDOTca wrote:
A question here for the experts.

I have an add-in that I reference in a workbook using VBE and ToolsReferences.

UDF's from this add-in can then be used in a CF formula.

If I reference FUNCRES.xla I still cannot use the ATP functions in CF formulas.

I assume the add-ins installed with Excel act differently from a regular
user-built add-in.

Correct?

Gord Dibben MS Excel MVP



On Tue, 20 Nov 2007 15:04:01 -0800, "Peo Sjoblom" wrote:
Since ATP is an add-in it exists physically on another sheet and you cannot
refer to other sheets when doing CF unless you use a defined name. You can
bypass ATP by putting ISODD in another cell hidden from view, or even better
in another sheet, for instance if the cell you want to CF is in Sheet1

=ISODD(Sheet1!A1)

if you put that in another sheet and call that cell for ODD
(insertnamedefine)

then you can select A1 in Sheet1 and use

=ODD=TRUE

and the CF will work

now you can hide this particular sheet with the isodd formula and nobody
will have a clue- Hide quoted text -

- Show quoted text -

conditional formating with isodd and iseven
 

A question here for the experts.

Well, that eliminates me. But I do know a little about this subject!

You can use functions from the ATP in CF *but* you can't use them directly
or Excel complains like the OP pointed out. You have to use them in a named
formula.

InsertNameDefine
Name: Odd
Refers to: =ISODD(INDIRECT("A"&ROW()))

Set CF
Select the range (assuming A1:An)
Formula Is: =Odd

If there's an easy workaround that bypasses the ATP functions then I'd use
that such as in this case using the MOD formulas.


--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
A question here for the experts.

I have an add-in that I reference in a workbook using VBE and
ToolsReferences.

UDF's from this add-in can then be used in a CF formula.

If I reference FUNCRES.xla I still cannot use the ATP functions in CF
formulas.

I assume the add-ins installed with Excel act differently from a regular
user-built add-in.

Correct?


Gord Dibben MS Excel MVP


On Tue, 20 Nov 2007 15:04:01 -0800, "Peo Sjoblom"
wrote:

Since ATP is an add-in it exists physically on another sheet and you
cannot
refer to other sheets when doing CF unless you use a defined name. You can
bypass ATP by putting ISODD in another cell hidden from view, or even
better
in another sheet, for instance if the cell you want to CF is in Sheet1

=ISODD(Sheet1!A1)

if you put that in another sheet and call that cell for ODD
(insertnamedefine)

then you can select A1 in Sheet1 and use

=ODD=TRUE

and the CF will work

now you can hide this particular sheet with the isodd formula and nobody
will have a clue

conditional formating with isodd and iseven
 

I know you can use a named range but that was not the question.

To access a UDF from a user-created add-in all I have to do is add a reference
to that add-in in ToolsReferences then call it directly in CF

Typical example..........add-in has this UDF and the add-in is referenced by my
open workbook.

Function IsFormula(Cell)
IsFormula = Cell.HasFormula
End Function

In CF I use Formula is: =IsFormula(cellref) to return true or false

Or =DocProps("Author")="Gord Dibben" using the DocProps function

This will not work with Excel add-ins AFAICS


Gord

On Tue, 20 Nov 2007 22:13:07 -0500, "T. Valko" wrote:

A question here for the experts.

Well, that eliminates me. But I do know a little about this subject!

You can use functions from the ATP in CF *but* you can't use them directly
or Excel complains like the OP pointed out. You have to use them in a named
formula.

InsertNameDefine
Name: Odd
Refers to: =ISODD(INDIRECT("A"&ROW()))

Set CF
Select the range (assuming A1:An)
Formula Is: =Odd

If there's an easy workaround that bypasses the ATP functions then I'd use
that such as in this case using the MOD formulas.

conditional formating with isodd and iseven
 

Ok, I see what you mean. I don't have an answer for that.

--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
I know you can use a named range but that was not the question.

To access a UDF from a user-created add-in all I have to do is add a
reference
to that add-in in ToolsReferences then call it directly in CF

Typical example..........add-in has this UDF and the add-in is referenced
by my
open workbook.

Function IsFormula(Cell)
IsFormula = Cell.HasFormula
End Function

In CF I use Formula is: =IsFormula(cellref) to return true or false

Or =DocProps("Author")="Gord Dibben" using the DocProps function

This will not work with Excel add-ins AFAICS


Gord

On Tue, 20 Nov 2007 22:13:07 -0500, "T. Valko"
wrote:

A question here for the experts.

Well, that eliminates me. But I do know a little about this subject!

You can use functions from the ATP in CF *but* you can't use them directly
or Excel complains like the OP pointed out. You have to use them in a
named
formula.

InsertNameDefine
Name: Odd
Refers to: =ISODD(INDIRECT("A"&ROW()))

Set CF
Select the range (assuming A1:An)
Formula Is: =Odd

If there's an easy workaround that bypasses the ATP functions then I'd use
that such as in this case using the MOD formulas.

conditional formating with isodd and iseven
 

On Nov 20, 10:26 pm, "T. Valko" wrote:
Ok, I see what you mean. I don't have an answer for that.

--
Biff
Microsoft Excel MVP

"Gord Dibben" <gorddibbATshawDOTca wrote in message

...



I know you can use a named range but that was not the question.

To access a UDF from a user-created add-in all I have to do is add a
reference
to that add-in in ToolsReferences then call it directly in CF

Typical example..........add-in has this UDF and the add-in is referenced
by my
open workbook.

Function IsFormula(Cell)
IsFormula = Cell.HasFormula
End Function

In CF I use Formula is: =IsFormula(cellref) to return true or false

Or =DocProps("Author")="Gord Dibben" using the DocProps function

This will not work with Excel add-ins AFAICS

Gord

On Tue, 20 Nov 2007 22:13:07 -0500, "T. Valko"
wrote:

A question here for the experts.

Well, that eliminates me. But I do know a little about this subject!

You can use functions from the ATP in CF *but* you can't use them directly
or Excel complains like the OP pointed out. You have to use them in a
named
formula.

InsertNameDefine
Name: Odd
Refers to: =ISODD(INDIRECT("A"&ROW()))

Set CF
Select the range (assuming A1:An)
Formula Is: =Odd

If there's an easy workaround that bypasses the ATP functions then I'd use
that such as in this case using the MOD formulas.- Hide quoted text -

- Show quoted text -

You Guys are working rocket science and all I wanted to do was crack a
rock. I think I'll just hit it with a hammer! I really appreciate you
all working on this and letting me and others learn from you. Some of
this is way over my head but some of it I can follow and put to use
later.

Respectfully

BigD

conditional formating with isodd and iseven
 

Sorry about going off on a bit of a tangent.

My query was off-topic.

Use the named range method Biff posted.


Gord

On Wed, 21 Nov 2007 07:03:24 -0800 (PST), BIG D
wrote:

You Guys are working rocket science and all I wanted to do was crack a
rock. I think I'll just hit it with a hammer! I really appreciate you
all working on this and letting me and others learn from you. Some of
this is way over my head but some of it I can follow and put to use
later.

Respectfully

BigD