Copying formula vertically, to pick up horizontal cell values
 

Without using copy , paste special, and then transposing what is copied. can
I write a formula, than when copied down or vertically, it picks up the
adjacent or horizontal cell values eg.

I want to copy C10 to C11 and cause it to pick up C4

1 A B C D
2
3 Jan Feb Mar
4 Budgeted Sales 100,000 200,000 400,000
5
6
7
8 Actual vs Budget
9 Actual Budget Variance
10 Jan 120,000.00 100,000.00 20,000.00
11 Feb 280,000.00
12 Mar 370,000.00
13 Apr 400,000.00
14 Total 1,170,000.00

Copying formula vertically, to pick up horizontal cell values
 

In C10 use....

=OFFSET($B$4,0,ROW($B4)-4)

Then copy down.

"Jammings" wrote:

Without using copy , paste special, and then transposing what is copied. can
I write a formula, than when copied down or vertically, it picks up the
adjacent or horizontal cell values eg.

I want to copy C10 to C11 and cause it to pick up C4

1 A B C D
2
3 Jan Feb Mar
4 Budgeted Sales 100,000 200,000 400,000
5
6
7
8 Actual vs Budget
9 Actual Budget Variance
10 Jan 120,000.00 100,000.00 20,000.00
11 Feb 280,000.00
12 Mar 370,000.00
13 Apr 400,000.00
14 Total 1,170,000.00

Copying formula vertically, to pick up horizontal cell values
 

Hi Jkiser
The formulae works fine.
To get a better understanding, what does ROW($B4) represents.

"jkiser" wrote:

In C10 use....

=OFFSET($B$4,0,ROW($B4)-4)

Then copy down.

"Jammings" wrote:

Without using copy , paste special, and then transposing what is copied. can
I write a formula, than when copied down or vertically, it picks up the
adjacent or horizontal cell values eg.

I want to copy C10 to C11 and cause it to pick up C4

1 A B C D
2
3 Jan Feb Mar
4 Budgeted Sales 100,000 200,000 400,000
5
6
7
8 Actual vs Budget
9 Actual Budget Variance
10 Jan 120,000.00 100,000.00 20,000.00
11 Feb 280,000.00
12 Mar 370,000.00
13 Apr 400,000.00
14 Total 1,170,000.00

Copying formula vertically, to pick up horizontal cell values
 

=ROW() just returns a number. The number is the row number in your excel
spread sheet. So =ROW($A$1) would return the number 1, =ROW($A$2) would
return the number 2, etc....

BTW...any time you want to find out what a formula does, select the cell
that has your formula in it, then select the "Formulas" tab at the top of the
excel sheet. Then pick "Insert Function" with the funny looking " fx " icon.
If that's not enough info, you can select the "Help on this function" link
to get lots of good info.

Good luck.

"Jammings" wrote:

Hi Jkiser
The formulae works fine.
To get a better understanding, what does ROW($B4) represents.

"jkiser" wrote:

In C10 use....

=OFFSET($B$4,0,ROW($B4)-4)

Then copy down.

"Jammings" wrote:

Without using copy , paste special, and then transposing what is copied. can
I write a formula, than when copied down or vertically, it picks up the
adjacent or horizontal cell values eg.

I want to copy C10 to C11 and cause it to pick up C4

1 A B C D
2
3 Jan Feb Mar
4 Budgeted Sales 100,000 200,000 400,000
5
6
7
8 Actual vs Budget
9 Actual Budget Variance
10 Jan 120,000.00 100,000.00 20,000.00
11 Feb 280,000.00
12 Mar 370,000.00
13 Apr 400,000.00
14 Total 1,170,000.00