If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autopopulating formulass horizontally?
If I create the formula in A1: =Worksheet!I4 I want to drag this to the right to copy the formula to other cells to the right. By default, A2 would equal: =Worksheet!J4 If I procede the I4 with a $, I get the same value all accross: A2: =Worksheet!$I4 A3: =Worksheet!$I4 How do I get A2: =Workstheet!I5 A3: =Worksheet!I6 etc. Thanks. |
#2
|
|||
|
|||
Autopopulating formulass horizontally?
I assume you mean,
A2: =Worksheet!I5 B2: =Worksheet!I6 One way is to have a row of numbers.. so in row 3, have 5, 6, 7 across the row. then, in A2: =INDIRECT("Worksheet!I"&A3) And copy across. The value in cell A2 would be the value in Worsheet!I5, then B2 would be I6, etc. "Leonhardtk" wrote: If I create the formula in A1: =Worksheet!I4 I want to drag this to the right to copy the formula to other cells to the right. By default, A2 would equal: =Worksheet!J4 If I procede the I4 with a $, I get the same value all accross: A2: =Worksheet!$I4 A3: =Worksheet!$I4 How do I get A2: =Workstheet!I5 A3: =Worksheet!I6 etc. Thanks. |
#3
|
|||
|
|||
Autopopulating formulass horizontally?
try this idea
=INDIRECT("j"&COLUMN(A1)) =INDIRECT("sourcesheetnamehere!j"&COLUMN(A1)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Leonhardtk" wrote in message ... If I create the formula in A1: =Worksheet!I4 I want to drag this to the right to copy the formula to other cells to the right. By default, A2 would equal: =Worksheet!J4 If I procede the I4 with a $, I get the same value all accross: A2: =Worksheet!$I4 A3: =Worksheet!$I4 How do I get A2: =Workstheet!I5 A3: =Worksheet!I6 etc. Thanks. |
#4
|
|||
|
|||
Autopopulating formulass horizontally?
If I create the formula in A1: =Worksheet!I4
I want to drag this to the right to copy the formula to other cells to the right How do I get A2: =Workstheet!I5 A3: =Worksheet!I6 If dragging to the right, the references should be B1=Worksheet!I5 & C1=Worksheet!I6 In this case you'll need to transpose a vertical source to a horizontal result. First select a range of cells on your result sheet beginning in A1 and extending the exact number of cells to the right as your source range (Worksheet4!I-) is long. In other words if your source is from I4 to I23 your result range will be A1:T1. Both ranges being 20 cells in length. With the cells selected enter this formula as an array formula: =TRANSPOSE(Worksheet!$I$4:$I$23) To commit the formula as an array formula, press Ctrl-Shift-Enter. If done correctly you'll see that each formula is enclosed in brackets{}. Best wishes. Dave "Leonhardtk" wrote in message ... If I create the formula in A1: =Worksheet!I4 I want to drag this to the right to copy the formula to other cells to the right. By default, A2 would equal: =Worksheet!J4 If I procede the I4 with a $, I get the same value all accross: A2: =Worksheet!$I4 A3: =Worksheet!$I4 How do I get A2: =Workstheet!I5 A3: =Worksheet!I6 etc. Thanks. |
#5
|
|||
|
|||
Autopopulating formulass horizontally?
Another way to transpose without the use of an array formula:
Place this formula in A1 and copy to the right as needed. =INDEX(Worksheet!$I$4:$I$23,COLUMN(),1) Dave "Leonhardtk" wrote in message ... If I create the formula in A1: =Worksheet!I4 I want to drag this to the right to copy the formula to other cells to the right. By default, A2 would equal: =Worksheet!J4 If I procede the I4 with a $, I get the same value all accross: A2: =Worksheet!$I4 A3: =Worksheet!$I4 How do I get A2: =Workstheet!I5 A3: =Worksheet!I6 etc. Thanks. |
Thread Tools | |
Display Modes | |
|
|