Cell Reference won't change

[Guest]

=SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON
07\[1DLSUNDAY.xlsm]601'!$I2:$U20=$A3)) - SUMPRODUCT(--('C:\Documents and
Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$W2:$AA20=A3))

I don't understand why the A3 won't change to A4 when I drag this formula
down I need to populate 100 cells in a column and don't want to have to
change it manually. As you can see I have tried both the $A3 and just A3 but
nothing changes. Please help me understand. Thanks

 

[Guest]

Hi,

Is you cell formatted as text??? If it is then format as "General" and try
again.


Regards!
Jean-Guy

 

[Guest]

Already tried that but thank you.

Hi,

Is you cell formatted as text??? If it is then format as "General" and try
again.


Regards!
Jean-Guy

=SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON
07\[1DLSUNDAY.xlsm]601'!$I2:$U20=$A3)) - SUMPRODUCT(--('C:\Documents and
Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$W2:$AA20=A3))

I don't understand why the A3 won't change to A4 when I drag this formula
down I need to populate 100 cells in a column and don't want to have to
change it manually. As you can see I have tried both the $A3 and just A3 but
nothing changes. Please help me understand. Thanks

 

[Dave Peterson]

I put that formula in A1 and dragged down.

I got a bunch of prompts to find that file, but after I dismissed those prompts,
both the $a3 and a3 were incremented.

Any chance that the formula is changing, but not recalculating--so you see the
same results in each cell?

If yes, then try
tools|options|calculation tab|check automatic.

=SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON
07\[1DLSUNDAY.xlsm]601'!$I2:$U20=$A3)) - SUMPRODUCT(--('C:\Documents and
Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$W2:$AA20=A3))

I don't understand why the A3 won't change to A4 when I drag this formula
down I need to populate 100 cells in a column and don't want to have to
change it manually. As you can see I have tried both the $A3 and just A3 but
nothing changes. Please help me understand. Thanks