How to use the drag box to copy?

[Scott82]

I have the following formula
=COUNTA('S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!$E$4,'S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!$E$7,'S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!$E$10,'S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!$E$13,'S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!$E$16,'S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!$E$19,'S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!$E$22,'S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!$E$25,'S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!$E$28)-9

and want it to copy for cell F, G, H etc.
When i use the drag box (bottom right of the cell) it just wants to copy exactly, how do I get excel to keep counting up??

Very confused....

Scott

 

[paddydive]

Hi,

Have u tried copy -paste formula for this ?

 

[VoG]

Try removing the $ signs to make the cell references relative.

 

[Scott82]

Thanks guys!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Removing the $ worked great!<o></o>
<o></o>
Why does excel put them on when using a different worksheet when it works just fine without??<o></o>
<o></o>
Scott<o></o>

 

[paddydive]

excel takes absolute reference by default.

 

[Scott82]

What do you mean Paddydive?

 

[VoG]

This may help you http://www.cpearson.com/excel/relative.aspx

 

[Scott82]

This may help you http://www.cpearson.com/excel/relative.aspx

Much clearer! Thanks Vog!

 

[paddydive]

Thanks VoG for explaining.