Copying Formulas

[Guest]

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?

 

[Pete_UK]

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

 

[Stan Brown]

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.