Excel Formula Help - Cell References

[DAnDan]

Bit of an odd question here, but I'm totally stuck and can't seen to work out how to do this!

Basically I want to have a column set up with the following:

In cell A1 "=B1-B800"
In cell A2 "=B2-B799"
In cell A3 "=B3-B798"

And so on, until they meet around 400. I could write this out manually, but it would obviously take quite a while! So is there an automatic way to do this? I tried the standard click and drag down the column, but it just adds 1 to both references each time, which isn't what I need.

Any suggestions?

 

[Lartyconshayo]

Should be fairly easy in VB. Just declare two variables and then loop them until either one reaches the target number while incrementing the cell reference by the integer counting up and inserting the formula within the loop.

I don't have to excel to hand to write this out but that would be for me a quick way to do it.

 

[andshrew]

One way you can do it without VBA is by using INDIRECT.

=B1-INDIRECT("B" & 801-ROW())

The way this works is by taking 801 as the starting value and subtracting the current row number from it, and therefore decreasing the cell referenced each time. The B1 part will still automatically increase as usual when you drag it down.

 

[Grrrrr]

You should avoid INDIRECT if possible because it's a volatile function. You can do almost exactly the same thing using index though

=B1 - INDEX($B1$B800, 801 - ROW() )