Copying Formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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?
Click to expand...

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.
 
Back
Top