Looking along a row to find specific values

[PhillyD]

I posted this question a few days ago but didn't really get anything I could
use, so in an attempt to phrase my question better, here goes:

I have a spreadsheet in to which users enter measurements. Each row is for
an individual asset, however, there are 8 readings entered on the row for
measurements along a particular asset. The columns are not sequential either!

I need a method which will:

1. Tell me of any values greater than or equal to 1439, but less than or
equal to 1449
2. Tell me of any values greater than or equal to 1450
3. Tell me of any values less than 1439.

I had thought conditional formatting would be the way ahead but I can't work
out how to get Excel to look along the row, rather than in just one cell.

Your help and patience is greatly appreciated.

Phil

 

[Pete_UK]

Which columns are used for your 8 readings?

How many rows of data do you have?

What colours do you want for your 3 conditions?

Pete

 

[PhillyD]

Hi Pete,

The data to be interrogated is in the following columns:

X, Y, AA, AB, AC, AD, AE
and AH.

Row 1 is a header, with rows 2 - 250 holding details of the assets
along with the measurement data.

Anything that does not meet either of the criteria should be green, those
over 1450 should be red and those between 1439 and 1449 yellow.

Many thanks for your time and patience.

Phil

 

[xlmate]

Try this

1) Select the range that you want to apply Conditonal Format, these cells
will then br highlighted
2) Format >> Conditional Formating
3) Under Condition 1,
a) select Cell Value Is
b) select between from the dropdown
c) type >=1439
d) type <=1449
4) Click on Format and go to Pattern and apply the yellow color you want

5) Under Conditon 2, repeat a) from the above
b) select greater than or equal to
c) type 1450 and apply the red color format
6) Under Condition 3, select less than and type 1439
then apply the green color format

if any of the cells value in the selected range are not equal to
condition 1, the cell will fill with Yellow
condition 2, Red
condition 3, Green

Is this what you want?

--
HTH

Pls provide your feedback by clicking the YES button below if this posting
is helpful
This will help others to search the results in the archive better

cheers, francis

 

[PhillyD]

Hi xlmate,

I can't use conditional formatting for two reasons:

1. Conditional formatting already exists in these cells to highlight values
reaching certain intervention limits
2. I need something that will interrogate the values along the row and only
act if it finds either >=1439, <=1449 OR >1450.

Ideally, if it was written in to a macro control button to only output the
records matching criteria set by the user it would be ideal but a simple
output to another worksheet of those assets matching the criteria would be
ideal for now.

Thanks for your suggestion.

 

[Pete_UK]

Well, this contradicts what you said originally, i.e.:

I had thought conditional formatting would be the way ahead ...

You could use a helper column (or 3) to indicate if any of the
conditions were met, eg on row 2:

=IF(OR
(X2>=1450,Y2>=1450,AA2>=1450,AB2>=1450,AC2>=1450,AD2>=1450,AE2>=1450,AH2>=1450),"Yes","")

in a column to check for greater than or equal to 1450,

=IF(OR
(X2<1439,Y2<1439,AA2<1439,AB2<1439,AC2<1439,AD2<1439,AE2<1439,AH2<1439),"Yes","")

in another column to check for less than 1439, and:

=IF(OR(AND(X2>=1439,X2<=1449),AND(Y2>=1439,Y2<=1449),AND
(AA2>=1439,AA2<=1449),AND(AB2>=1439,AB2<=1449),AND
(AC2>=1439,AC2<=1449),AND(AD2>=1439,AD2<=1449),AND
(AE2>=1439,AE2<=1449),AND(AH2>=1439,AH2<=1449)),"Yes","")

in a third column to check for your other condition.

Then you can copy these three formulae down to row 250, and you will
get Yes in a cell when the conditions are met for each row.

Hope this helps.

Pete