I am using an index match formula to evaluate items on a summary sheet based
on data input on other sheets. I am then desiring to create conditional cell
formatting based on the index/match formula of the summary sheet. The data
input sheets are text strings and when I use the index/match formula, the
items are displayed correctly, but when I make the attempt to use a
conditional format rule for the row based on the cell containing the
index/match formula, it seems as if all items are "true" when applying istext
for the "formula is" criteria. Is there any way to use cell type set to blank
on the summary sheet if there is nothing in the data input sheet? How to use
this in conjunction with conditional format on the summary sheet if based on
using istext for 2 columns?

Not enough detail.

I am using an index match formula

The result of an INDEX MATCH formula can be either text or numeric (or an
error). If the result of this formula looks like a number but CF is being
applied using an =ISTEXT(...) formula then the result of the INDEX MATCH
formula is a TEXT value. So, the problem would be the data that the INDEX
MATCH formula is "pulling" from.

Need more detail!

--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
I am using an index match formula to evaluate items on a summary sheet
based
on data input on other sheets. I am then desiring to create conditional
cell
formatting based on the index/match formula of the summary sheet. The data
input sheets are text strings and when I use the index/match formula, the
items are displayed correctly, but when I make the attempt to use a
conditional format rule for the row based on the cell containing the
index/match formula, it seems as if all items are "true" when applying
istext
for the "formula is" criteria. Is there any way to use cell type set to
blank
on the summary sheet if there is nothing in the data input sheet? How to
use
this in conjunction with conditional format on the summary sheet if based
on
using istext for 2 columns?

Thanks for the reply. The index/match formula (Col L of sheet2) is looking at
sheet1 A2:L36 for a match of Col B (person's name), and if a match goes to
Col D for text string input (ex: "1-2 years"). On sheet2 the conditional
format of a row (Col A - Col T), it was suggested I use a "helper column"
(ex: Col Z) using the formula:

=ISTEXT(INDEX(Sheet1!$D:$D,MATCH(Sheet2!$B2,Sheet1 !$B:$B,0)))

and then use CF formula is

=$Z2

This worked fine, but I now have a second section of sheet1 with text string
input in Col F and I used the following index/match formula based on person's
name to populate Col M of sheet2:

=IF(ISERROR(INDEX(Sheet1!$A$36:$L$48,MATCH(Sheet2! B10,Sheet1!$B$36:$B$48,0),6)),"",IF(INDEX(Sheet1!$ A$36:$L$48,MATCH(Sheet2!B10,Sheet1!$B$36:$B$48,0), 6)=0,"",INDEX(Sheet1!$A$36:$L$48,MATCH(Sheet2!B10, Sheet1!$B$36:$B$48,0),6)))

In the helper column (Col V), I tried the formula:

=ISTEXT(INDEX(Sheet1!$F:$F,MATCH(Sheet2!$B4,Sheet1 !$B:$B,0)))

and all cells of Sheet2 Col V = FALSE. Why?

End result I would like to be able to base the CF on the TRUE/FALSE results
of Col U and Col V for each row of Sheet2.

HELP!?!?!?


"T. Valko" wrote:

Not enough detail.

I am using an index match formula

The result of an INDEX MATCH formula can be either text or numeric (or an
error). If the result of this formula looks like a number but CF is being
applied using an =ISTEXT(...) formula then the result of the INDEX MATCH
formula is a TEXT value. So, the problem would be the data that the INDEX
MATCH formula is "pulling" from.

Need more detail!

--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
I am using an index match formula to evaluate items on a summary sheet
based
on data input on other sheets. I am then desiring to create conditional
cell
formatting based on the index/match formula of the summary sheet. The data
input sheets are text strings and when I use the index/match formula, the
items are displayed correctly, but when I make the attempt to use a
conditional format rule for the row based on the cell containing the
index/match formula, it seems as if all items are "true" when applying
istext
for the "formula is" criteria. Is there any way to use cell type set to
blank
on the summary sheet if there is nothing in the data input sheet? How to
use
this in conjunction with conditional format on the summary sheet if based
on
using istext for 2 columns?

HELP!?!?!?

I'm having a hard time trying to visualize what you're trying to do. If I
could see the file I'm sure I could figure it out. Any chance you can send
me a copy of the file?


--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
Thanks for the reply. The index/match formula (Col L of sheet2) is looking
at
sheet1 A2:L36 for a match of Col B (person's name), and if a match goes to
Col D for text string input (ex: "1-2 years"). On sheet2 the conditional
format of a row (Col A - Col T), it was suggested I use a "helper column"
(ex: Col Z) using the formula:

=ISTEXT(INDEX(Sheet1!$D:$D,MATCH(Sheet2!$B2,Sheet1 !$B:$B,0)))

and then use CF formula is

=$Z2

This worked fine, but I now have a second section of sheet1 with text
string
input in Col F and I used the following index/match formula based on
person's
name to populate Col M of sheet2:

=IF(ISERROR(INDEX(Sheet1!$A$36:$L$48,MATCH(Sheet2! B10,Sheet1!$B$36:$B$48,0),6)),"",IF(INDEX(Sheet1!$ A$36:$L$48,MATCH(Sheet2!B10,Sheet1!$B$36:$B$48,0), 6)=0,"",INDEX(Sheet1!$A$36:$L$48,MATCH(Sheet2!B10, Sheet1!$B$36:$B$48,0),6)))

In the helper column (Col V), I tried the formula:

=ISTEXT(INDEX(Sheet1!$F:$F,MATCH(Sheet2!$B4,Sheet1 !$B:$B,0)))

and all cells of Sheet2 Col V = FALSE. Why?

End result I would like to be able to base the CF on the TRUE/FALSE
results
of Col U and Col V for each row of Sheet2.

HELP!?!?!?


"T. Valko" wrote:

Not enough detail.

I am using an index match formula

The result of an INDEX MATCH formula can be either text or numeric (or an
error). If the result of this formula looks like a number but CF is being
applied using an =ISTEXT(...) formula then the result of the INDEX MATCH
formula is a TEXT value. So, the problem would be the data that the INDEX
MATCH formula is "pulling" from.

Need more detail!

--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
I am using an index match formula to evaluate items on a summary sheet
based
on data input on other sheets. I am then desiring to create conditional
cell
formatting based on the index/match formula of the summary sheet. The
data
input sheets are text strings and when I use the index/match formula,
the
items are displayed correctly, but when I make the attempt to use a
conditional format rule for the row based on the cell containing the
index/match formula, it seems as if all items are "true" when applying
istext
for the "formula is" criteria. Is there any way to use cell type set to
blank
on the summary sheet if there is nothing in the data input sheet? How
to
use
this in conjunction with conditional format on the summary sheet if
based
on
using istext for 2 columns?

would be happy to send to you. let me know where to send...

"T. Valko" wrote:

HELP!?!?!?

I'm having a hard time trying to visualize what you're trying to do. If I
could see the file I'm sure I could figure it out. Any chance you can send
me a copy of the file?


--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
Thanks for the reply. The index/match formula (Col L of sheet2) is looking
at
sheet1 A2:L36 for a match of Col B (person's name), and if a match goes to
Col D for text string input (ex: "1-2 years"). On sheet2 the conditional
format of a row (Col A - Col T), it was suggested I use a "helper column"
(ex: Col Z) using the formula:

=ISTEXT(INDEX(Sheet1!$D:$D,MATCH(Sheet2!$B2,Sheet1 !$B:$B,0)))

and then use CF formula is

=$Z2

This worked fine, but I now have a second section of sheet1 with text
string
input in Col F and I used the following index/match formula based on
person's
name to populate Col M of sheet2:

=IF(ISERROR(INDEX(Sheet1!$A$36:$L$48,MATCH(Sheet2! B10,Sheet1!$B$36:$B$48,0),6)),"",IF(INDEX(Sheet1!$ A$36:$L$48,MATCH(Sheet2!B10,Sheet1!$B$36:$B$48,0), 6)=0,"",INDEX(Sheet1!$A$36:$L$48,MATCH(Sheet2!B10, Sheet1!$B$36:$B$48,0),6)))

In the helper column (Col V), I tried the formula:

=ISTEXT(INDEX(Sheet1!$F:$F,MATCH(Sheet2!$B4,Sheet1 !$B:$B,0)))

and all cells of Sheet2 Col V = FALSE. Why?

End result I would like to be able to base the CF on the TRUE/FALSE
results
of Col U and Col V for each row of Sheet2.

HELP!?!?!?


"T. Valko" wrote:

Not enough detail.

I am using an index match formula

The result of an INDEX MATCH formula can be either text or numeric (or an
error). If the result of this formula looks like a number but CF is being
applied using an =ISTEXT(...) formula then the result of the INDEX MATCH
formula is a TEXT value. So, the problem would be the data that the INDEX
MATCH formula is "pulling" from.

Need more detail!

--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
I am using an index match formula to evaluate items on a summary sheet
based
on data input on other sheets. I am then desiring to create conditional
cell
formatting based on the index/match formula of the summary sheet. The
data
input sheets are text strings and when I use the index/match formula,
the
items are displayed correctly, but when I make the attempt to use a
conditional format rule for the row based on the cell containing the
index/match formula, it seems as if all items are "true" when applying
istext
for the "formula is" criteria. Is there any way to use cell type set to
blank
on the summary sheet if there is nothing in the data input sheet? How
to
use
this in conjunction with conditional format on the summary sheet if
based
on
using istext for 2 columns?

I'm at:

xl can help at comcast period net

Remove "can" and all the spaces and make the obvious changes. If the file is
big, 1mb, zip it. (compress it)

--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
would be happy to send to you. let me know where to send...

"T. Valko" wrote:

HELP!?!?!?

I'm having a hard time trying to visualize what you're trying to do. If I
could see the file I'm sure I could figure it out. Any chance you can
send
me a copy of the file?


--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
Thanks for the reply. The index/match formula (Col L of sheet2) is
looking
at
sheet1 A2:L36 for a match of Col B (person's name), and if a match goes
to
Col D for text string input (ex: "1-2 years"). On sheet2 the
conditional
format of a row (Col A - Col T), it was suggested I use a "helper
column"
(ex: Col Z) using the formula:

=ISTEXT(INDEX(Sheet1!$D:$D,MATCH(Sheet2!$B2,Sheet1 !$B:$B,0)))

and then use CF formula is

=$Z2

This worked fine, but I now have a second section of sheet1 with text
string
input in Col F and I used the following index/match formula based on
person's
name to populate Col M of sheet2:

=IF(ISERROR(INDEX(Sheet1!$A$36:$L$48,MATCH(Sheet2! B10,Sheet1!$B$36:$B$48,0),6)),"",IF(INDEX(Sheet1!$ A$36:$L$48,MATCH(Sheet2!B10,Sheet1!$B$36:$B$48,0), 6)=0,"",INDEX(Sheet1!$A$36:$L$48,MATCH(Sheet2!B10, Sheet1!$B$36:$B$48,0),6)))

In the helper column (Col V), I tried the formula:

=ISTEXT(INDEX(Sheet1!$F:$F,MATCH(Sheet2!$B4,Sheet1 !$B:$B,0)))

and all cells of Sheet2 Col V = FALSE. Why?

End result I would like to be able to base the CF on the TRUE/FALSE
results
of Col U and Col V for each row of Sheet2.

HELP!?!?!?


"T. Valko" wrote:

Not enough detail.

I am using an index match formula

The result of an INDEX MATCH formula can be either text or numeric (or
an
error). If the result of this formula looks like a number but CF is
being
applied using an =ISTEXT(...) formula then the result of the INDEX
MATCH
formula is a TEXT value. So, the problem would be the data that the
INDEX
MATCH formula is "pulling" from.

Need more detail!

--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
I am using an index match formula to evaluate items on a summary
sheet
based
on data input on other sheets. I am then desiring to create
conditional
cell
formatting based on the index/match formula of the summary sheet.
The
data
input sheets are text strings and when I use the index/match
formula,
the
items are displayed correctly, but when I make the attempt to use a
conditional format rule for the row based on the cell containing the
index/match formula, it seems as if all items are "true" when
applying
istext
for the "formula is" criteria. Is there any way to use cell type set
to
blank
on the summary sheet if there is nothing in the data input sheet?
How
to
use
this in conjunction with conditional format on the summary sheet if
based
on
using istext for 2 columns?

Thanks. File just sent.

"T. Valko" wrote:

I'm at:

xl can help at comcast period net

Remove "can" and all the spaces and make the obvious changes. If the file is
big, 1mb, zip it. (compress it)

--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
would be happy to send to you. let me know where to send...

"T. Valko" wrote:

HELP!?!?!?

I'm having a hard time trying to visualize what you're trying to do. If I
could see the file I'm sure I could figure it out. Any chance you can
send
me a copy of the file?


--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
Thanks for the reply. The index/match formula (Col L of sheet2) is
looking
at
sheet1 A2:L36 for a match of Col B (person's name), and if a match goes
to
Col D for text string input (ex: "1-2 years"). On sheet2 the
conditional
format of a row (Col A - Col T), it was suggested I use a "helper
column"
(ex: Col Z) using the formula:

=ISTEXT(INDEX(Sheet1!$D:$D,MATCH(Sheet2!$B2,Sheet1 !$B:$B,0)))

and then use CF formula is

=$Z2

This worked fine, but I now have a second section of sheet1 with text
string
input in Col F and I used the following index/match formula based on
person's
name to populate Col M of sheet2:

=IF(ISERROR(INDEX(Sheet1!$A$36:$L$48,MATCH(Sheet2! B10,Sheet1!$B$36:$B$48,0),6)),"",IF(INDEX(Sheet1!$ A$36:$L$48,MATCH(Sheet2!B10,Sheet1!$B$36:$B$48,0), 6)=0,"",INDEX(Sheet1!$A$36:$L$48,MATCH(Sheet2!B10, Sheet1!$B$36:$B$48,0),6)))

In the helper column (Col V), I tried the formula:

=ISTEXT(INDEX(Sheet1!$F:$F,MATCH(Sheet2!$B4,Sheet1 !$B:$B,0)))

and all cells of Sheet2 Col V = FALSE. Why?

End result I would like to be able to base the CF on the TRUE/FALSE
results
of Col U and Col V for each row of Sheet2.

HELP!?!?!?


"T. Valko" wrote:

Not enough detail.

I am using an index match formula

The result of an INDEX MATCH formula can be either text or numeric (or
an
error). If the result of this formula looks like a number but CF is
being
applied using an =ISTEXT(...) formula then the result of the INDEX
MATCH
formula is a TEXT value. So, the problem would be the data that the
INDEX
MATCH formula is "pulling" from.

Need more detail!

--
Biff
Microsoft Excel MVP


"CEG_Staffer" wrote in message
...
I am using an index match formula to evaluate items on a summary
sheet
based
on data input on other sheets. I am then desiring to create
conditional
cell
formatting based on the index/match formula of the summary sheet.
The
data
input sheets are text strings and when I use the index/match
formula,
the
items are displayed correctly, but when I make the attempt to use a
conditional format rule for the row based on the cell containing the
index/match formula, it seems as if all items are "true" when
applying
istext
for the "formula is" criteria. Is there any way to use cell type set
to
blank
on the summary sheet if there is nothing in the data input sheet?
How
to
use
this in conjunction with conditional format on the summary sheet if
based
on
using istext for 2 columns?