Autopopulating formulass horizontally?

Leonhardtk · External Since: Jul 09, 2007 Posts: 15

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.

Sean Timmons · External Since: Dec 17, 2006 Posts: 372

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.
>

Don Guillett · External Since: Jan 04, 2006 Posts: 2988

try this idea
=INDIRECT("j"&COLUMN(A1))
=INDIRECT("sourcesheetnamehere!j"&COLUMN(A1))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1.DeleteThis@austin.rr.com
"Leonhardtk" <Leonhardtk.DeleteThis@discussions.microsoft.com> wrote in message
news:7AA28FA6-816F-46BF-A7BB-1F751D4B8AB8@microsoft.com...
>
> 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.
>

Bassman62 · External Since: Mar 26, 2009 Posts: 10

> 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" <Leonhardtk DeleteThis @discussions.microsoft.com> wrote in message
news:7AA28FA6-816F-46BF-A7BB-1F751D4B8AB8@microsoft.com...
>
> 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.
>

Bassman62 · External Since: Mar 26, 2009 Posts: 10

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" <Leonhardtk DeleteThis @discussions.microsoft.com> wrote in message
news:7AA28FA6-816F-46BF-A7BB-1F751D4B8AB8@microsoft.com...
>
> 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.
>