I would like to compare a single value from one list in a worksheet to a
range of values in the same worksheet. I use the array formula
=OR(EXACT(singlevalue, startofrange:endofrange)). I then try to auto-fill
cells beneath the original formula, but when I do the cell labels represented
by "startofrange' and "endofrange" increment by 1 for every cell I move down.
So that means that if I start with "singlevalue" at A1, startofrange at B1
and endofrange at B10, by the time I have autofilled down to "A4"
startofrange is B4 and endofrange is B14. So, instead of comparing each cell
to the same comparison range I am comparing each cell to a DIFFERENT
comparison range, excluding some of the cells I want at the top and including
ones I don't want at the bottom. Is there any way I can prevent excel from
incrementing just one part of the formula I mentioned above so I don't have
to go back and fix some 400 formulas? Thanks!

Make your reference absolute
Say if your range is A1:A100 make it look like this
$A$1:$A$100

by inserting dollar signs at places that you want NOT to increment




"LossOfSignal" wrote in message
...
I would like to compare a single value from one list in a worksheet to a
range of values in the same worksheet. I use the array formula
=OR(EXACT(singlevalue, startofrange:endofrange)). I then try to auto-fill
cells beneath the original formula, but when I do the cell labels
represented
by "startofrange' and "endofrange" increment by 1 for every cell I move
down.
So that means that if I start with "singlevalue" at A1, startofrange at B1
and endofrange at B10, by the time I have autofilled down to "A4"
startofrange is B4 and endofrange is B14. So, instead of comparing each
cell
to the same comparison range I am comparing each cell to a DIFFERENT
comparison range, excluding some of the cells I want at the top and
including
ones I don't want at the bottom. Is there any way I can prevent excel
from
incrementing just one part of the formula I mentioned above so I don't
have
to go back and fix some 400 formulas? Thanks!

That worked perfectly. Thanks for your time!

"LossOfSignal" wrote:

I would like to compare a single value from one list in a worksheet to a
range of values in the same worksheet. I use the array formula
=OR(EXACT(singlevalue, startofrange:endofrange)). I then try to auto-fill
cells beneath the original formula, but when I do the cell labels represented
by "startofrange' and "endofrange" increment by 1 for every cell I move down.
So that means that if I start with "singlevalue" at A1, startofrange at B1
and endofrange at B10, by the time I have autofilled down to "A4"
startofrange is B4 and endofrange is B14. So, instead of comparing each cell
to the same comparison range I am comparing each cell to a DIFFERENT
comparison range, excluding some of the cells I want at the top and including
ones I don't want at the bottom. Is there any way I can prevent excel from
incrementing just one part of the formula I mentioned above so I don't have
to go back and fix some 400 formulas? Thanks!