I have an array formula that compares a master list with an updated list, and
shows any new items: =IF(OR(EXACT('New Task Page'!C2,'5.2- Task
Report'!C2:C6500)),"",'New Task Page'!C2) . I want to drag the formula down
the column so that the next cell reads: =IF(OR(EXACT('New Task Page'!C3,'5.2-
Task Report'!C2:C6500)),"",'New Task Page'!C3) . The next cell down should
just change the C3's to C4's, and so on. Unfortunately, it changes the
formula to: =IF(OR(EXACT('New Task Page'!C3,'5.2- Task
Report'!C3:C6501)),"",'New Task Page'!C3), basically adding 1 to every number
in the formula. If I fix the C3 cell, then group C2 and C3 and drag down, it
still adds 1 to each number in the formula. Without having to manually fix
each column, how can I drag the formula so that only the first and last C's
add 1, and not the C2:C6500 column?

If you don't want C2:C6500 to change, then make it C$2:C$6500 in your
formula (top cell), then copy it down.

Hope this helps.

Pete

On Apr 20, 4:48 pm, archsmooth
wrote:
I have an array formula that compares a master list with an updated list, and
shows any new items: =IF(OR(EXACT('New Task Page'!C2,'5.2- Task
Report'!C2:C6500)),"",'New Task Page'!C2) . I want to drag the formula down
the column so that the next cell reads: =IF(OR(EXACT('New Task Page'!C3,'5.2-
Task Report'!C2:C6500)),"",'New Task Page'!C3) . The next cell down should
just change the C3's to C4's, and so on. Unfortunately, it changes the
formula to: =IF(OR(EXACT('New Task Page'!C3,'5.2- Task
Report'!C3:C6501)),"",'New Task Page'!C3), basically adding 1 to every number
in the formula. If I fix the C3 cell, then group C2 and C3 and drag down, it
still adds 1 to each number in the formula. Without having to manually fix
each column, how can I drag the formula so that only the first and last C's
add 1, and not the C2:C6500 column?

Fri, 20 Apr 2007 08:48:02 -0700 from archsmooth
:
I have an array formula that compares a master list with an updated list, and
shows any new items: =IF(OR(EXACT('New Task Page'!C2,'5.2- Task
Report'!C2:C6500)),"",'New Task Page'!C2) . I want to drag the formula down
the column so that the next cell reads: =IF(OR(EXACT('New Task Page'!C3,'5.2-
Task Report'!C2:C6500)),"",'New Task Page'!C3) . The next cell down should
just change the C3's to C4's, and so on. Unfortunately, it changes the
formula to: =IF(OR(EXACT('New Task Page'!C3,'5.2- Task
Report'!C3:C6501)),"",'New Task Page'!C3), basically adding 1 to every number
in the formula. If I fix the C3 cell, then group C2 and C3 and drag down, it
still adds 1 to each number in the formula. Without having to manually fix
each column, how can I drag the formula so that only the first and last C's
add 1, and not the C2:C6500 column?

Look up "absolute and relative references" in Help.

If I understand you correctly, you can change
C2:C65000
to
C$2:C$65000
and it will do what you want. But read up on references in the help.
You pretty much have to understand that material to work efficiently
in Excel.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/