Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) |