I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6 is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using the
value of A2 in the calculation instead of looking at the formula, I never get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formulas text or you have any other suggestions, PLEASE, PLEASE let me know.

Thanks,
Sarah Jane Bowers

Imo, easiest way is to use a helper col (say col K) where you would indicate
say, a "2" in the col for such rows (A one-time manual pain, applied
retrospectively for what you already have on the sheet). Then just apply CF
in this manner.

Select the entire sheet (with A1 active)
Under Condition 1,
Formula is: =$K1=2
Format the fill/font, etc to taste OK out
The above would then conditionally highlight entire rows
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sarah Jane" wrote:
I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6 is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using the
value of A2 in the calculation instead of looking at the formula, I never get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formulas text or you have any other suggestions, PLEASE, PLEASE let me know.

Thanks,
Sarah Jane Bowers

Hi!

Use Formula Is and just use the same formula but add the /2 to every one.

Whatever Index formula you have in A1:

=A1=INDEX(.....................))/2

I don't know how to word this but maybe you'll get what I'm trying to
describe. If not I'll try again!

Biff

"Sarah Jane" wrote in message
...
I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6
is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells
that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using
the
value of A2 in the calculation instead of looking at the formula, I never
get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want
to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formula's text or you have any other suggestions, PLEASE, PLEASE let me
know.

Thanks,
Sarah Jane Bowers

Thanks Max. Believe it or not, I thought of your suggestion shortly after I
posted the question; however, since I run several other macros on this data,
I can't change the column placements by adding another column. For now, I
may try to use column IV since this particular dataset hasn't YET maxed out
the number of columns available in Excel.

I have another workbook with a similar situation that has already maxed out
the number of columns, so if you think of another solution.....

"Max" wrote:

Imo, easiest way is to use a helper col (say col K) where you would indicate
say, a "2" in the col for such rows (A one-time manual pain, applied
retrospectively for what you already have on the sheet). Then just apply CF
in this manner.

Select the entire sheet (with A1 active)
Under Condition 1,
Formula is: =$K1=2
Format the fill/font, etc to taste OK out
The above would then conditionally highlight entire rows
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sarah Jane" wrote:
I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6 is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using the
value of A2 in the calculation instead of looking at the formula, I never get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formulas text or you have any other suggestions, PLEASE, PLEASE let me know.

Thanks,
Sarah Jane Bowers

Thanks Biff. I understand what you are saying, and that is a great idea, but
conditional formatting won't allow you to use references to other worksheets
or workbooks. When I tried, Excel gave me an error message.

"Biff" wrote:

Hi!

Use Formula Is and just use the same formula but add the /2 to every one.

Whatever Index formula you have in A1:

=A1=INDEX(.....................))/2

I don't know how to word this but maybe you'll get what I'm trying to
describe. If not I'll try again!

Biff

"Sarah Jane" wrote in message
...
I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6
is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells
that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using
the
value of A2 in the calculation instead of looking at the formula, I never
get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want
to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formula's text or you have any other suggestions, PLEASE, PLEASE let me
know.

Thanks,
Sarah Jane Bowers

Ooops! I wasn't paying attention with regards to the other sheet.

Is there anything unique about the values that are divided and those that
are not?

Biff

"Sarah Jane" wrote in message
...
Thanks Biff. I understand what you are saying, and that is a great idea,
but
conditional formatting won't allow you to use references to other
worksheets
or workbooks. When I tried, Excel gave me an error message.

"Biff" wrote:

Hi!

Use Formula Is and just use the same formula but add the /2 to every one.

Whatever Index formula you have in A1:

=A1=INDEX(.....................))/2

I don't know how to word this but maybe you'll get what I'm trying to
describe. If not I'll try again!

Biff

"Sarah Jane" wrote in message
...
I have several cells with formulas in them, and other cells have the
same
formula divided by 2. My real formulas are rather complex, i.e. Cell
X6
is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells
that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is
using
the
value of A2 in the calculation instead of looking at the formula, I
never
get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool
Excel
into thinking the cell was formatted as text, but that didn't work
either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper
columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't
want
to
have to run a macro every time I edit a cell or add more rows or
columns.
Therefore, if you know of an Excel formula that I can use to access a
formula's text or you have any other suggestions, PLEASE, PLEASE let me
know.

Thanks,
Sarah Jane Bowers

I can't really think of anything unique. The ones being divided represent
common charges. In other words, some charges go to Program A only; some go
to Program B only; and some need to be split evenly between Program A and
Program B. I have manually highlighted (the only thing that shows
uniqueness) the charge number that the formula references in column D, but I
haven't figured out a way to make the conditional formatting criteria look at
another cell's format. Do you know how?

"Biff" wrote:

Ooops! I wasn't paying attention with regards to the other sheet.

Is there anything unique about the values that are divided and those that
are not?

Biff

"Sarah Jane" wrote in message
...
Thanks Biff. I understand what you are saying, and that is a great idea,
but
conditional formatting won't allow you to use references to other
worksheets
or workbooks. When I tried, Excel gave me an error message.

"Biff" wrote:

Hi!

Use Formula Is and just use the same formula but add the /2 to every one.

Whatever Index formula you have in A1:

=A1=INDEX(.....................))/2

I don't know how to word this but maybe you'll get what I'm trying to
describe. If not I'll try again!

Biff

"Sarah Jane" wrote in message
...
I have several cells with formulas in them, and other cells have the
same
formula divided by 2. My real formulas are rather complex, i.e. Cell
X6
is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells
that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is
using
the
value of A2 in the calculation instead of looking at the formula, I
never
get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool
Excel
into thinking the cell was formatted as text, but that didn't work
either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper
columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't
want
to
have to run a macro every time I edit a cell or add more rows or
columns.
Therefore, if you know of an Excel formula that I can use to access a
formula's text or you have any other suggestions, PLEASE, PLEASE let me
know.

Thanks,
Sarah Jane Bowers

You'd have to use a VBA UDF and helper cells to identify the color but that
just defeats the purpose.

You can use a named formula that refers to other sheets in conditional
formatting BUT the references have to be absolute.

=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$ 1,act!$1:$1,0))/2

You could probably rewrite that formula and use an INDIRECT or OFFSET
expression to define $D6 and X$1 BUT you said you have 1000's of cells to
format and the use of INDIRECT or OFFSET may slow things down.

It seems that using a helper column with a marker like Max suggested may be
your BEST option.

Biff

"Sarah Jane" wrote in message
...
I can't really think of anything unique. The ones being divided represent
common charges. In other words, some charges go to Program A only; some
go
to Program B only; and some need to be split evenly between Program A and
Program B. I have manually highlighted (the only thing that shows
uniqueness) the charge number that the formula references in column D, but
I
haven't figured out a way to make the conditional formatting criteria look
at
another cell's format. Do you know how?

"Biff" wrote:

Ooops! I wasn't paying attention with regards to the other sheet.

Is there anything unique about the values that are divided and those that
are not?

Biff

"Sarah Jane" wrote in message
...
Thanks Biff. I understand what you are saying, and that is a great
idea,
but
conditional formatting won't allow you to use references to other
worksheets
or workbooks. When I tried, Excel gave me an error message.

"Biff" wrote:

Hi!

Use Formula Is and just use the same formula but add the /2 to every
one.

Whatever Index formula you have in A1:

=A1=INDEX(.....................))/2

I don't know how to word this but maybe you'll get what I'm trying to
describe. If not I'll try again!

Biff

"Sarah Jane" wrote in message
...
I have several cells with formulas in them, and other cells have the
same
formula divided by 2. My real formulas are rather complex, i.e.
Cell
X6
is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the
cells
that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is
using
the
value of A2 in the calculation instead of looking at the formula, I
never
get
a true condition. Then I thought about trying to replace the A2 in
the
conditional formula mentioned above with CONCATENATE("'",A2) to fool
Excel
into thinking the cell was formatted as text, but that didn't work
either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper
columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I
don't
want
to
have to run a macro every time I edit a cell or add more rows or
columns.
Therefore, if you know of an Excel formula that I can use to access
a
formula's text or you have any other suggestions, PLEASE, PLEASE let
me
know.

Thanks,
Sarah Jane Bowers

Perhaps this could work ..

Use Edit Replace on say col A to temporarily render the formulas into text.
Find: =
Replace with say: xx
Replace All

Then put in K1, and copy down:
=IF(RIGHT(A1,2)="/2",2,"")
This marks all the rows at one go

Kill the formulas in col K (copy paste special as values "in-place"), then
restore col A's formulas with a "reverse" Edit Replace, viz: Find: xx,
Replace with: =, Replace All. Then just apply the CF as per earlier.

As for:
.. another workbook with a similar situation
that has already maxed out the number of columns

Essentially the same method, but here we've to use a helper sheet and an
INDIRECT in the CF in the source sheet to get this up.

Assume the source sheetname is X. After edit replace to temporarily
convert col A into text, in a new sheet named: Y, place in A1 and copy down:
=IF(RIGHT(x!A1,2)="/2",2,"")

Then kill the formulas in Y's col A and restore X's col A's formulas
(reverse the edit replace). Then select the entire sheet X (with A1 active)
and apply the cond format formula as:
=INDIRECT("'Y'!A"&ROW(A1))=2
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sarah Jane" wrote:
Thanks Max. Believe it or not, I thought of your suggestion shortly after I
posted the question; however, since I run several other macros on this data,
I can't change the column placements by adding another column. For now, I
may try to use column IV since this particular dataset hasn't YET maxed out
the number of columns available in Excel.

I have another workbook with a similar situation that has already maxed out
the number of columns, so if you think of another solution.....

Why not creating a UDF that returns a cell formula and use it in your
conditional formatting conditions ?
--
Festina Lente


"Sarah Jane" wrote:

I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6 is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using the
value of A2 in the calculation instead of looking at the formula, I never get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formulas text or you have any other suggestions, PLEASE, PLEASE let me know.

Thanks,
Sarah Jane Bowers

I am not familiar with UDF. Can you give me some additional information
about it?
Thanks,
Sarah Jane

"PapaDos" wrote:

Why not creating a UDF that returns a cell formula and use it in your
conditional formatting conditions ?
--
Festina Lente


"Sarah Jane" wrote:

I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6 is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using the
value of A2 in the calculation instead of looking at the formula, I never get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formulas text or you have any other suggestions, PLEASE, PLEASE let me know.

Thanks,
Sarah Jane Bowers

"User defined function", a custom macro that you can use as a worksheet
function.
In your case a simple one would do, like:

Function getCellFormula(r As Range)
getCellFormula = r.Formula
End Function

If you enter =getcellFormula(a1) in a cell, the cell will contain the
formula of the A1 cell...

So for your conditional formatting, try this formula:
=RIGHT(SUBSTITUTE(getCellFormula(B11), " ", ""), 2) = "/2"
--
Festina Lente


"Sarah Jane" wrote:

I am not familiar with UDF. Can you give me some additional information
about it?
Thanks,
Sarah Jane

"PapaDos" wrote:

Why not creating a UDF that returns a cell formula and use it in your
conditional formatting conditions ?
--
Festina Lente


"Sarah Jane" wrote:

I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6 is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using the
value of A2 in the calculation instead of looking at the formula, I never get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formulas text or you have any other suggestions, PLEASE, PLEASE let me know.

Thanks,
Sarah Jane Bowers

THANK YOU! THANK YOU! THANK YOU!

I finally got back to that project to try your suggestion. It worked
perfectly!!!

Now I don't have to worry if I forgot to add or remove the "/2" in certain
cells because the cells with the "/2" are highlighted which makes it easy to
see if I got all the appropriate cells. Again, thank you so much.

Sarah Jane

"PapaDos" wrote:

"User defined function", a custom macro that you can use as a worksheet
function.
In your case a simple one would do, like:

Function getCellFormula(r As Range)
getCellFormula = r.Formula
End Function

If you enter =getcellFormula(a1) in a cell, the cell will contain the
formula of the A1 cell...

So for your conditional formatting, try this formula:
=RIGHT(SUBSTITUTE(getCellFormula(B11), " ", ""), 2) = "/2"
--
Festina Lente


"Sarah Jane" wrote:

I am not familiar with UDF. Can you give me some additional information
about it?
Thanks,
Sarah Jane

"PapaDos" wrote:

Why not creating a UDF that returns a cell formula and use it in your
conditional formatting conditions ?
--
Festina Lente


"Sarah Jane" wrote:

I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6 is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using the
value of A2 in the calculation instead of looking at the formula, I never get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formulas text or you have any other suggestions, PLEASE, PLEASE let me know.

Thanks,
Sarah Jane Bowers

Bienvenue !
:-]

--
Regards,
Luc.

"Festina Lente"


"Sarah Jane" wrote:

THANK YOU! THANK YOU! THANK YOU!

I finally got back to that project to try your suggestion. It worked
perfectly!!!

Now I don't have to worry if I forgot to add or remove the "/2" in certain
cells because the cells with the "/2" are highlighted which makes it easy to
see if I got all the appropriate cells. Again, thank you so much.

Sarah Jane

"PapaDos" wrote:

"User defined function", a custom macro that you can use as a worksheet
function.
In your case a simple one would do, like:

Function getCellFormula(r As Range)
getCellFormula = r.Formula
End Function

If you enter =getcellFormula(a1) in a cell, the cell will contain the
formula of the A1 cell...

So for your conditional formatting, try this formula:
=RIGHT(SUBSTITUTE(getCellFormula(B11), " ", ""), 2) = "/2"
--
Festina Lente


"Sarah Jane" wrote:

I am not familiar with UDF. Can you give me some additional information
about it?
Thanks,
Sarah Jane

"PapaDos" wrote:

Why not creating a UDF that returns a cell formula and use it in your
conditional formatting conditions ?
--
Festina Lente


"Sarah Jane" wrote:

I have several cells with formulas in them, and other cells have the same
formula divided by 2. My real formulas are rather complex, i.e. Cell X6 is
=INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2.
So, here is a very simplified example:
A B C
row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2
row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3
row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4
row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2

I want to use the conditional formatting option to highlight the cells that
are divided by 2. I tried "Formula Is
=IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using the
value of A2 in the calculation instead of looking at the formula, I never get
a true condition. Then I thought about trying to replace the A2 in the
conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel
into thinking the cell was formatted as text, but that didn't work either
because it still returned the value--only with an ' in front of it.

I have thousands of cells using these formulas, so creating helper columns
with text versions of the formulas is not possible, or at least not
practical. I think I could accomplish this with a macro, but I don't want to
have to run a macro every time I edit a cell or add more rows or columns.
Therefore, if you know of an Excel formula that I can use to access a
formulas text or you have any other suggestions, PLEASE, PLEASE let me know.

Thanks,
Sarah Jane Bowers