Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing Data listed Vertically into Horizontal Table | Excel Discussion (Misc queries) | |||
copying formula for horizontal data to vertical in different sheet | Excel Worksheet Functions | |||
Horizontal Data arranged Vertically | Excel Discussion (Misc queries) | |||
Lookdown cell vertically, return horizontal value | Excel Discussion (Misc queries) | |||
How do I link horizontal cells vertically? | Excel Discussion (Misc queries) |