Relative column referencing within formulas
 

Hi.

I am having difficulty whilst creating formulas.

My formula is of the form:

B1=A1-C1
B2=A2-D2
B3=A3-E3
.......

I wish to fill the formula down a single column (ie, column B).

Whilst using the auto-fill option, the formula will not increment the column
reference in subsequent rows.

For example, the next row 4 in my worksheet will display:

B4=A4-C4

when in fact I wish this to be:

B4=A4-F4.

Can you please assist in helping me to solve this problem.

Many thanks.

Relative column referencing within formulas
 

Try this in B1 and copy down:

=A1-INDEX($C$1:$Z$24,ROWS($1:1),ROWS($1:1))

Adjust the range of your data to suit.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Alistair" wrote in message
...
Hi.

I am having difficulty whilst creating formulas.

My formula is of the form:

B1=A1-C1
B2=A2-D2
B3=A3-E3
.......

I wish to fill the formula down a single column (ie, column B).

Whilst using the auto-fill option, the formula will not increment the column
reference in subsequent rows.

For example, the next row 4 in my worksheet will display:

B4=A4-C4

when in fact I wish this to be:

B4=A4-F4.

Can you please assist in helping me to solve this problem.

Many thanks.

Relative column referencing within formulas
 

This might work for you:

B1 = A1 - OFFSET(B1,0,ROW())

Then just copy down.

HTH,
Elkar


"Alistair" wrote:

Hi.

I am having difficulty whilst creating formulas.

My formula is of the form:

B1=A1-C1
B2=A2-D2
B3=A3-E3
......

I wish to fill the formula down a single column (ie, column B).

Whilst using the auto-fill option, the formula will not increment the column
reference in subsequent rows.

For example, the next row 4 in my worksheet will display:

B4=A4-C4

when in fact I wish this to be:

B4=A4-F4.

Can you please assist in helping me to solve this problem.

Many thanks.

Relative column referencing within formulas
 

Try something like:

=A1-OFFSET(A1,0,ROW(2:2))

HTH

Jean-Guy

"Alistair" wrote:

Hi.

I am having difficulty whilst creating formulas.

My formula is of the form:

B1=A1-C1
B2=A2-D2
B3=A3-E3
......

I wish to fill the formula down a single column (ie, column B).

Whilst using the auto-fill option, the formula will not increment the column
reference in subsequent rows.

For example, the next row 4 in my worksheet will display:

B4=A4-C4

when in fact I wish this to be:

B4=A4-F4.

Can you please assist in helping me to solve this problem.

Many thanks.

Relative column referencing within formulas
 

That's worked a treat.

Many thanks.

"RagDyeR" wrote:

Try this in B1 and copy down:

=A1-INDEX($C$1:$Z$24,ROWS($1:1),ROWS($1:1))

Adjust the range of your data to suit.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Alistair" wrote in message
...
Hi.

I am having difficulty whilst creating formulas.

My formula is of the form:

B1=A1-C1
B2=A2-D2
B3=A3-E3
.......

I wish to fill the formula down a single column (ie, column B).

Whilst using the auto-fill option, the formula will not increment the column
reference in subsequent rows.

For example, the next row 4 in my worksheet will display:

B4=A4-C4

when in fact I wish this to be:

B4=A4-F4.

Can you please assist in helping me to solve this problem.

Many thanks.