Hi All,

Is this possible with a Formula?...

I would like to retrieve the Minimum and Maximum from numerous columns with
cells that already have Conditional Formatting applied to them. The CF
formats the cell interior red. Can the Min and Max values be found within the
CF red interior cells and returned to an empty cell.

Thanks,
Sam

--
Message posted via http://www.officekb.com

See http://www.xldynamic.com/source/xld.ColourCounter.html

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cb4431d96cf@uwe...
Hi All,

Is this possible with a Formula?...

I would like to retrieve the Minimum and Maximum from numerous columns
with
cells that already have Conditional Formatting applied to them. The CF
formats the cell interior red. Can the Min and Max values be found within
the
CF red interior cells and returned to an empty cell.

Thanks,
Sam

--
Message posted via http://www.officekb.com

Hi Bob,

Thank you very much for your assistance.

I've had a read through the text entitled Processing Coloured Cells.
Unfortunately, I think I'm still stuck based on the quoted constraint listed:

"the second shortcoming is that this technique at present does not cater for
cells that are coloured due to conditional formatting."

I think this excludes my scenario from using any of the techniques. Have I
understood this correctly?

Cheers,
Sam



Bob Phillips wrote:
See http://www.xldynamic.com/source/xld.ColourCounter.html

Hi All,

[quoted text clipped - 7 lines]
Thanks,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

You have understood correctly. There is another paper on CF there
http://www.xldynamic.com/source/xld.CFConditions.html

As to the quoted constraint, if you build it into SUMPRODUCT, you can add
another condition there.
--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cbf4a6b5e73@uwe...
Hi Bob,

Thank you very much for your assistance.

I've had a read through the text entitled Processing Coloured Cells.
Unfortunately, I think I'm still stuck based on the quoted constraint
listed:

"the second shortcoming is that this technique at present does not cater
for
cells that are coloured due to conditional formatting."

I think this excludes my scenario from using any of the techniques. Have I
understood this correctly?

Cheers,
Sam



Bob Phillips wrote:
See http://www.xldynamic.com/source/xld.ColourCounter.html

Hi All,

[quoted text clipped - 7 lines]
Thanks,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

Hi Bob,

Thanks again for input. I've read through the paper starting with Testing CF
Conditions and tried your neat Functions CFColorCount at
http://www.xldynamic.com/source/xld.CFConditions.html
and ColorIndex at http://www.xldynamic.com/source/xld.ColourCounter.html

I'm trying to work with your SUMPRODUCT suggestion:
As to the quoted constraint, if you build it into SUMPRODUCT, you can add another condition there.

Is using the CFColorCount Function with SUMPRODUCT part of the solution?
Cannot get required result.

Further help appreciated.

Cheers,
Sam

Bob Phillips wrote:
You have understood correctly. There is another paper on CF there
http://www.xldynamic.com/source/xld.CFConditions.html

As to the quoted constraint, if you build it into SUMPRODUCT, you can add
another condition there.
Hi Bob,

[quoted text clipped - 19 lines]
Thanks,
Sam

--
Message posted via http://www.officekb.com

Sam,

I am going to the cricket international today, so I will take a look
tomorrow.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cfb43c50122@uwe...
Hi Bob,

Thanks again for input. I've read through the paper starting with Testing
CF
Conditions and tried your neat Functions CFColorCount at
http://www.xldynamic.com/source/xld.CFConditions.html
and ColorIndex at http://www.xldynamic.com/source/xld.ColourCounter.html

I'm trying to work with your SUMPRODUCT suggestion:
As to the quoted constraint, if you build it into SUMPRODUCT, you can add
another condition there.

Is using the CFColorCount Function with SUMPRODUCT part of the solution?
Cannot get required result.

Further help appreciated.

Cheers,
Sam

Bob Phillips wrote:
You have understood correctly. There is another paper on CF there
http://www.xldynamic.com/source/xld.CFConditions.html

As to the quoted constraint, if you build it into SUMPRODUCT, you can add
another condition there.
Hi Bob,

[quoted text clipped - 19 lines]
Thanks,
Sam

--
Message posted via http://www.officekb.com

If the cells have cf applied to them then you should be able to write a
formula based on the logic that has been used in the cf'ing to find the
min/max.

Why are the cells formatted red?

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cb4431d96cf@uwe...
Hi All,

Is this possible with a Formula?...

I would like to retrieve the Minimum and Maximum from numerous columns
with
cells that already have Conditional Formatting applied to them. The CF
formats the cell interior red. Can the Min and Max values be found within
the
CF red interior cells and returned to an empty cell.

Thanks,
Sam

--
Message posted via http://www.officekb.com

Hi Biff,

This is the Conditional Format Formula =COUNTIF(INDEX(Data,MATCH($H$13,ID,0),
0),$G17)

The cells are formatted red when a specific ID is matched and its associated
numeric value in the Dynamic Named Range "Data"

Cheers,
Sam

Biff wrote:
If the cells have cf applied to them then you should be able to write a
formula based on the logic that has been used in the cf'ing to find the
min/max.

Why are the cells formatted red?

Biff

Hi All,

[quoted text clipped - 9 lines]
Thanks,
Sam

--
Message posted via http://www.officekb.com

Need the details. A small chunk of sample data with expected result would
help.

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cdea6908411@uwe...
Hi Biff,

This is the Conditional Format Formula
=COUNTIF(INDEX(Data,MATCH($H$13,ID,0),
0),$G17)

The cells are formatted red when a specific ID is matched and its
associated
numeric value in the Dynamic Named Range "Data"

Cheers,
Sam

Biff wrote:
If the cells have cf applied to them then you should be able to write a
formula based on the logic that has been used in the cf'ing to find the
min/max.

Why are the cells formatted red?

Biff

Hi All,

[quoted text clipped - 9 lines]
Thanks,
Sam

--
Message posted via http://www.officekb.com

Hi Biff,

Sample Data using ID 10621

Cell Ref H13 = 10621
Column "G" = Numeric Labels
Column "H" = Frequency Values

Column "G" Column"H"
Numeric Label Frequency
Row17 90 10
Row18 480 7
Row19 80 5
Row20 60 4
Row21 50 4
Row22 470 3
Row23 430 2
Row24 420 1


ID 10621 will have several Numeric Labels in its row. If any of the above
Numeric Labels match with ID 10621 associated row values then the Numeric
Label's adjacent Frequency value should be highlighted in Red.

The Numeric Labels are defined in a 12 column by many rows Dynamic Named
Range called "Data".

This is the Conditional Format Formula
=COUNTIF(INDEX(Data,MATCH($H$13,ID,0),0),$G17)

CF Formula applied to column "H" (Frequency values)


Cheers,
Sam

Biff wrote:
Need the details. A small chunk of sample data with expected result would
help.

Biff

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

Well, this is something I would need to see for myself. I'm just not getting
a good "picture" of the data. If you want to/can send me a copy of the file
I'll get a better idea of what needs to be done. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cf486263e96@uwe...
Hi Biff,

Sample Data using ID 10621

Cell Ref H13 = 10621
Column "G" = Numeric Labels
Column "H" = Frequency Values

Column "G" Column"H"
Numeric Label Frequency
Row17 90 10
Row18 480 7
Row19 80 5
Row20 60 4
Row21 50 4
Row22 470 3
Row23 430 2
Row24 420 1


ID 10621 will have several Numeric Labels in its row. If any of the above
Numeric Labels match with ID 10621 associated row values then the Numeric
Label's adjacent Frequency value should be highlighted in Red.

The Numeric Labels are defined in a 12 column by many rows Dynamic Named
Range called "Data".

This is the Conditional Format Formula
=COUNTIF(INDEX(Data,MATCH($H$13,ID,0),0),$G17)

CF Formula applied to column "H" (Frequency values)


Cheers,
Sam

Biff wrote:
Need the details. A small chunk of sample data with expected result would
help.

Biff

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1