Is there an easier way?

[Bigpond]

am entering a formula that is counting items in a resource tracking
register
I Pasted 2000 entries along a row for example
=IF($B$2=FW1,$D$2-$H$2,0)
(FW1 is the item number)
I now want to copy this row down 2000 rows
so I have to change each column header to allow for downward copying example
=IF(B2=$FW$1,D2-H2,0)

I have tried writing a macro that will change example 1 to example 2, but no
matter how I try I find that the cell refences chang (D2-e2 and so on). I
need of course to be able to have these change (to c3, c4 etc) when I copy
down, so I cant use the $ signs here.

Manually it is taking forever, can anyone come up with a saving for me .

Thanks

 

[Niek Otten]

I'm not sure I understand what you require.
But try this:

=IF($B2=$FW$1,$D2-$H2,0)

That is, $ signs only for the column, not for the row

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| am entering a formula that is counting items in a resource tracking
| register
| I Pasted 2000 entries along a row for example
| =IF($B$2=FW1,$D$2-$H$2,0)
| (FW1 is the item number)
| I now want to copy this row down 2000 rows
| so I have to change each column header to allow for downward copying example
| =IF(B2=$FW$1,D2-H2,0)
|
| I have tried writing a macro that will change example 1 to example 2, but no
| matter how I try I find that the cell refences chang (D2-e2 and so on). I
| need of course to be able to have these change (to c3, c4 etc) when I copy
| down, so I cant use the $ signs here.
|
| Manually it is taking forever, can anyone come up with a saving for me .
|
| Thanks
|
|
|

 

[Bigpond]

Thanks a lot it was exactly what I wanted, it has saved me hours and hours
regards

Peter Braun