Some help in refernceing some cells

Hi there,

I need help.
I have a long list of numbers, and in each cell next to each number, I want to display a color if that number appeared in the last 42 numbers.
So if it did - color "Green"
and if not - color " RED"

PLEASE HELP!! Anybody!!
Thanks
KK

Use conditional formatting.

Select row 43 down and use this formula

=AND(ROW()>42,COUNTIF(A1:A42,A43)>0)

--
HTH

Bob Phillips

"kylekoopman" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi there,
>
> I need help.
> I have a long list of numbers, and in each cell next to each number, I
> want to display a color if that number appeared in the last 42 numbers.
>
> So if it did - color "Green"
> and if not - color " RED"
>
> PLEASE HELP!! Anybody!!
> Thanks
> KK
>
>
> --
> kylekoopman
> ------------------------------------------------------------------------
> kylekoopman's Profile:
http://www.excelforum.com/member.php...o&userid=27361
> View this thread: http://www.excelforum.com/showthread...hreadid=468695
>

Hi KK

What does "last 42 numbers" mean.. Do you mean number that has appeared
in previous 42 rows above the current row in a unbroken sequence...
Give an example if possible.....

Thanks
Karthik Bhat

It means this.....

Ok in Column "A" I have numbers in each cell all the way down to cell 100
Now in column "B" I want to display a color (green or red) depending on if the number next to that cell in column A appeared in the last 42 numbers of column A.

example.

Cell A64 displays a 6
Next to it in Cell B64 I want it to display GREEN because in the 42 cells above A64, there were two 6's.

Another example.

Cell A49 displays a 12
Next to it in cell B49 I want it to display RED because the number 12 has not appeared in the last 42 cells above A49.

So basically I just want colum B referencing the last 42 cells from column A, and displaying GREEN if the number beside it in column A appeared, or RED for VICE VERSA.

Hope that helps.
Thanks
KK

Thanks Bob,
your suggestion worked like a charm

KK

May be like this :

Fill this in cell B:43
=IF(NOT(ISNA(VLOOKUP(A43,INDIRECT("A"&ROW()-42&":A"&ROW()-1),1,FALSE))),"TRU
E","FALSE")

then copy down to the last row you want, so we've "TRUE" and "FALSE" value
in colom B

and then use conditional formating
Format|Conditional Formatting

condition 1 : Cell Value Is | Equal To | ="TRUE"
change the format to GREEN

condition 2 : Cell Value Is | Equal To | ="FALSE"
change the format to RED

may be .....





"kylekoopman" <[email protected]>
wrote in message
news:[email protected]...
>
> It means this.....
>
> Ok in Column "A" I have numbers in each cell all the way down to cell
> 100
> Now in column "B" I want to display a color (green or red) depending on
> if the number next to that cell in column A appeared in the last 42
> numbers of column A.
>
> example.
>
> Cell A64 displays a 6
> Next to it in Cell B64 I want it to display GREEN because in the 42
> cells above A64, there were two 6's.
>
> Another example.
>
> Cell A49 displays a 12
> Next to it in cell B49 I want it to display RED because the number 12
> has not appeared in the last 42 cells above A49.
>
> So basically I just want colum B referencing the last 42 cells from
> column A, and displaying GREEN if the number beside it in column A
> appeared, or RED for VICE VERSA.
>
> Hope that helps.
> Thanks
> KK
>
>
> --
> kylekoopman
> ------------------------------------------------------------------------
> kylekoopman's Profile:
http://www.excelforum.com/member.php...o&userid=27361
> View this thread: http://www.excelforum.com/showthread...hreadid=468695
>