Hi,

I am trying to figure out a means to copy a formula across multiple columns
so that the row value increments automatically but not the column value. For
example:

The first cell, A1, has a formula of: =Sheet1!K110
After copy/paste, Cell B1 should have: =Sheet1!K114
After copy/paste, Cell C1 should have: =Sheet1!K118
After copy/paste, Cell B1 should have: =Sheet1!K122

The sheet I am working on has probably 200 such cells so being able to do
this via copy/paste would be ideal compared to editing each cell one-by-one.

Thank you for your help.

PS

Try this:

=INDEX(Sheet1!$K110:$K300,COLUMNS($A1:A1)*4-3)

Copy across as needed. Adjust the end of range: $K300 as needed.

--
Biff
Microsoft Excel MVP


"Paul" <(E-Mail Removed)> wrote in message
news:7C70050F-4EA5-40DD-B026-(E-Mail Removed)...
> Hi,
>
> I am trying to figure out a means to copy a formula across multiple
> columns
> so that the row value increments automatically but not the column value.
> For
> example:
>
> The first cell, A1, has a formula of: =Sheet1!K110
> After copy/paste, Cell B1 should have: =Sheet1!K114
> After copy/paste, Cell C1 should have: =Sheet1!K118
> After copy/paste, Cell B1 should have: =Sheet1!K122
>
> The sheet I am working on has probably 200 such cells so being able to do
> this via copy/paste would be ideal compared to editing each cell
> one-by-one.
>
> Thank you for your help.
>
> PS
>

Hi Biff,

Thanks alot, this worked great!

PS


"T. Valko" wrote:

> Try this:
>
> =INDEX(Sheet1!$K110:$K300,COLUMNS($A1:A1)*4-3)
>
> Copy across as needed. Adjust the end of range: $K300 as needed.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Paul" <(E-Mail Removed)> wrote in message
> news:7C70050F-4EA5-40DD-B026-(E-Mail Removed)...
> > Hi,
> >
> > I am trying to figure out a means to copy a formula across multiple
> > columns
> > so that the row value increments automatically but not the column value.
> > For
> > example:
> >
> > The first cell, A1, has a formula of: =Sheet1!K110
> > After copy/paste, Cell B1 should have: =Sheet1!K114
> > After copy/paste, Cell C1 should have: =Sheet1!K118
> > After copy/paste, Cell B1 should have: =Sheet1!K122
> >
> > The sheet I am working on has probably 200 such cells so being able to do
> > this via copy/paste would be ideal compared to editing each cell
> > one-by-one.
> >
> > Thank you for your help.
> >
> > PS
> >
>
>
>

Hi,

Here is another option:

=INDIRECT("Sheet1!K"&106+COLUMN()*4)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Paul" wrote:

> Hi,
>
> I am trying to figure out a means to copy a formula across multiple columns
> so that the row value increments automatically but not the column value. For
> example:
>
> The first cell, A1, has a formula of: =Sheet1!K110
> After copy/paste, Cell B1 should have: =Sheet1!K114
> After copy/paste, Cell C1 should have: =Sheet1!K118
> After copy/paste, Cell B1 should have: =Sheet1!K122
>
> The sheet I am working on has probably 200 such cells so being able to do
> this via copy/paste would be ideal compared to editing each cell one-by-one.
>
> Thank you for your help.
>
> PS
>

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Paul" <(E-Mail Removed)> wrote in message
news2701E5F-06FF-402A-B698-(E-Mail Removed)...
> Hi Biff,
>
> Thanks alot, this worked great!
>
> PS
>
>
> "T. Valko" wrote:
>
>> Try this:
>>
>> =INDEX(Sheet1!$K110:$K300,COLUMNS($A1:A1)*4-3)
>>
>> Copy across as needed. Adjust the end of range: $K300 as needed.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Paul" <(E-Mail Removed)> wrote in message
>> news:7C70050F-4EA5-40DD-B026-(E-Mail Removed)...
>> > Hi,
>> >
>> > I am trying to figure out a means to copy a formula across multiple
>> > columns
>> > so that the row value increments automatically but not the column
>> > value.
>> > For
>> > example:
>> >
>> > The first cell, A1, has a formula of: =Sheet1!K110
>> > After copy/paste, Cell B1 should have: =Sheet1!K114
>> > After copy/paste, Cell C1 should have: =Sheet1!K118
>> > After copy/paste, Cell B1 should have: =Sheet1!K122
>> >
>> > The sheet I am working on has probably 200 such cells so being able to
>> > do
>> > this via copy/paste would be ideal compared to editing each cell
>> > one-by-one.
>> >
>> > Thank you for your help.
>> >
>> > PS
>> >
>>
>>
>>

[djlitheng]

same case but diff row
for example :


The cell Sheet2! B32, has a formula of: =Sheet1!C9
After copy/paste, Cell Sheet2!B33 should have: =Sheet1!C47
After copy/paste, Cell Sheet2!B34 should have: =Sheet1!C85
After copy/paste, Cell Sheet2!B35 should have: =Sheet1!C123

Your help will be much appreciated.