# Dragging & incrementing cell values

1. ## Dragging & incrementing cell values

Not sure if there is an easy way around this ...

From time to time I seem to find myself needing to create cell references
between sheets in the same workbook where the source data is stored
'vertically', yet I need the results to be shown 'horizontally'.

As a simple example, if Sheet 1 contains data in column A, cells 1 - 100,
and I want to link this to cells in Sheet 2 in Row 1 (ie; A1, B1, C1, etc),
how can I do this other than manually typing the cell references each time?

On Sheet 2 I need to see - A1: =Sheet1!A1, B1: =Sheet1!A2, etc.

Thanks!

2. ## RE: Dragging & incrementing cell values

Try this formula on Sheet2:

=OFFSET(sheet1!\$A\$1,0,COLUMN()-1,0)

Then copy across your columns.

HTH,
Elkar

"Terry Bennett" wrote:

> Not sure if there is an easy way around this ...
>
> From time to time I seem to find myself needing to create cell references
> between sheets in the same workbook where the source data is stored
> 'vertically', yet I need the results to be shown 'horizontally'.
>
> As a simple example, if Sheet 1 contains data in column A, cells 1 - 100,
> and I want to link this to cells in Sheet 2 in Row 1 (ie; A1, B1, C1, etc),
> how can I do this other than manually typing the cell references each time?
>
> On Sheet 2 I need to see - A1: =Sheet1!A1, B1: =Sheet1!A2, etc.
>
> Thanks!
>
>
>

3. ## Re: Dragging & incrementing cell values

That just gives me a load of #REF! error messages.

"Elkar" <Elkar@discussions.microsoft.com> wrote in message
news:EA741453-A05A-431A-9FA0-1D1D25B9148B@microsoft.com...
> Try this formula on Sheet2:
>
> =OFFSET(sheet1!\$A\$1,0,COLUMN()-1,0)
>
> Then copy across your columns.
>
> HTH,
> Elkar
>
>
> "Terry Bennett" wrote:
>
>> Not sure if there is an easy way around this ...
>>
>> From time to time I seem to find myself needing to create cell references
>> between sheets in the same workbook where the source data is stored
>> 'vertically', yet I need the results to be shown 'horizontally'.
>>
>> As a simple example, if Sheet 1 contains data in column A, cells 1 - 100,
>> and I want to link this to cells in Sheet 2 in Row 1 (ie; A1, B1, C1,
>> etc),
>> how can I do this other than manually typing the cell references each
>> time?
>>
>> On Sheet 2 I need to see - A1: =Sheet1!A1, B1: =Sheet1!A2, etc.
>>
>> Thanks!
>>
>>
>>

4. ## Re: Dragging & incrementing cell values

Try this

=OFFSET(Sheet1!\$A\$1,COLUMN()-1,0)

copied across will work

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

"Terry Bennett" <terry.bennett1@virgin.net> wrote in message
> That just gives me a load of #REF! error messages.
>
> "Elkar" <Elkar@discussions.microsoft.com> wrote in message
> news:EA741453-A05A-431A-9FA0-1D1D25B9148B@microsoft.com...
>> Try this formula on Sheet2:
>>
>> =OFFSET(sheet1!\$A\$1,0,COLUMN()-1,0)
>>
>> Then copy across your columns.
>>
>> HTH,
>> Elkar
>>
>>
>> "Terry Bennett" wrote:
>>
>>> Not sure if there is an easy way around this ...
>>>
>>> From time to time I seem to find myself needing to create cell
>>> references
>>> between sheets in the same workbook where the source data is stored
>>> 'vertically', yet I need the results to be shown 'horizontally'.
>>>
>>> As a simple example, if Sheet 1 contains data in column A, cells 1 -
>>> 100,
>>> and I want to link this to cells in Sheet 2 in Row 1 (ie; A1, B1, C1,
>>> etc),
>>> how can I do this other than manually typing the cell references each
>>> time?
>>>
>>> On Sheet 2 I need to see - A1: =Sheet1!A1, B1: =Sheet1!A2, etc.
>>>
>>> Thanks!
>>>
>>>
>>>

>
>

5. ## Re: Dragging & incrementing cell values

Looks like the same as Elkar suggested which didn't work?

"Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:OcEGSPyWGHA.1480@TK2MSFTNGP03.phx.gbl...
> Try this
>
> =OFFSET(Sheet1!\$A\$1,COLUMN()-1,0)
>
> copied across will work
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> www.nwexcelsolutions.com
>
> (remove ^^ from email address)
>
> Portland, Oregon
>
>
>
>
> "Terry Bennett" <terry.bennett1@virgin.net> wrote in message
>> That just gives me a load of #REF! error messages.
>>
>> "Elkar" <Elkar@discussions.microsoft.com> wrote in message
>> news:EA741453-A05A-431A-9FA0-1D1D25B9148B@microsoft.com...
>>> Try this formula on Sheet2:
>>>
>>> =OFFSET(sheet1!\$A\$1,0,COLUMN()-1,0)
>>>
>>> Then copy across your columns.
>>>
>>> HTH,
>>> Elkar
>>>
>>>
>>> "Terry Bennett" wrote:
>>>
>>>> Not sure if there is an easy way around this ...
>>>>
>>>> From time to time I seem to find myself needing to create cell
>>>> references
>>>> between sheets in the same workbook where the source data is stored
>>>> 'vertically', yet I need the results to be shown 'horizontally'.
>>>>
>>>> As a simple example, if Sheet 1 contains data in column A, cells 1 -
>>>> 100,
>>>> and I want to link this to cells in Sheet 2 in Row 1 (ie; A1, B1, C1,
>>>> etc),
>>>> how can I do this other than manually typing the cell references each
>>>> time?
>>>>
>>>> On Sheet 2 I need to see - A1: =Sheet1!A1, B1: =Sheet1!A2, etc.
>>>>
>>>> Thanks!
>>>>
>>>>
>>>>

>>
>>

>

6. ## Re: Dragging & incrementing cell values

No it's not!, Elkar put the column function in the wrong place, however this
version is more stable

=OFFSET(Sheet1!\$A\$1,COLUMNS(\$A\$1:A1)-1,0)

since it will adapt to column insertions

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

"Terry Bennett" <terry.bennett1@virgin.net> wrote in message
news:uwK8QWyWGHA.3672@TK2MSFTNGP02.phx.gbl...
> Looks like the same as Elkar suggested which didn't work?
>
> "Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
> news:OcEGSPyWGHA.1480@TK2MSFTNGP03.phx.gbl...
>> Try this
>>
>> =OFFSET(Sheet1!\$A\$1,COLUMN()-1,0)
>>
>> copied across will work
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> Northwest Excel Solutions
>>
>> www.nwexcelsolutions.com
>>
>> (remove ^^ from email address)
>>
>> Portland, Oregon
>>
>>
>>
>>
>> "Terry Bennett" <terry.bennett1@virgin.net> wrote in message
>>> That just gives me a load of #REF! error messages.
>>>
>>> "Elkar" <Elkar@discussions.microsoft.com> wrote in message
>>> news:EA741453-A05A-431A-9FA0-1D1D25B9148B@microsoft.com...
>>>> Try this formula on Sheet2:
>>>>
>>>> =OFFSET(sheet1!\$A\$1,0,COLUMN()-1,0)
>>>>
>>>> Then copy across your columns.
>>>>
>>>> HTH,
>>>> Elkar
>>>>
>>>>
>>>> "Terry Bennett" wrote:
>>>>
>>>>> Not sure if there is an easy way around this ...
>>>>>
>>>>> From time to time I seem to find myself needing to create cell
>>>>> references
>>>>> between sheets in the same workbook where the source data is stored
>>>>> 'vertically', yet I need the results to be shown 'horizontally'.
>>>>>
>>>>> As a simple example, if Sheet 1 contains data in column A, cells 1 -
>>>>> 100,
>>>>> and I want to link this to cells in Sheet 2 in Row 1 (ie; A1, B1, C1,
>>>>> etc),
>>>>> how can I do this other than manually typing the cell references each
>>>>> time?
>>>>>
>>>>> On Sheet 2 I need to see - A1: =Sheet1!A1, B1: =Sheet1!A2, etc.
>>>>>
>>>>> Thanks!
>>>>>
>>>>>
>>>>>
>>>
>>>

>>

>
>

7. ## Re: Dragging & incrementing cell values

Thanks very much - that seems to work fine.

"Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:O%23F1\$ZyWGHA.4144@TK2MSFTNGP04.phx.gbl...
> No it's not!, Elkar put the column function in the wrong place, however
> this version is more stable
>
> =OFFSET(Sheet1!\$A\$1,COLUMNS(\$A\$1:A1)-1,0)
>
> since it will adapt to column insertions
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> www.nwexcelsolutions.com
>
> (remove ^^ from email address)
>
> Portland, Oregon
>
>
>
>
> "Terry Bennett" <terry.bennett1@virgin.net> wrote in message
> news:uwK8QWyWGHA.3672@TK2MSFTNGP02.phx.gbl...
>> Looks like the same as Elkar suggested which didn't work?
>>
>> "Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
>> news:OcEGSPyWGHA.1480@TK2MSFTNGP03.phx.gbl...
>>> Try this
>>>
>>> =OFFSET(Sheet1!\$A\$1,COLUMN()-1,0)
>>>
>>> copied across will work
>>>
>>> --
>>>
>>> Regards,
>>>
>>> Peo Sjoblom
>>>
>>> Northwest Excel Solutions
>>>
>>> www.nwexcelsolutions.com
>>>
>>> (remove ^^ from email address)
>>>
>>> Portland, Oregon
>>>
>>>
>>>
>>>
>>> "Terry Bennett" <terry.bennett1@virgin.net> wrote in message
>>>> That just gives me a load of #REF! error messages.
>>>>
>>>> "Elkar" <Elkar@discussions.microsoft.com> wrote in message
>>>> news:EA741453-A05A-431A-9FA0-1D1D25B9148B@microsoft.com...
>>>>> Try this formula on Sheet2:
>>>>>
>>>>> =OFFSET(sheet1!\$A\$1,0,COLUMN()-1,0)
>>>>>
>>>>> Then copy across your columns.
>>>>>
>>>>> HTH,
>>>>> Elkar
>>>>>
>>>>>
>>>>> "Terry Bennett" wrote:
>>>>>
>>>>>> Not sure if there is an easy way around this ...
>>>>>>
>>>>>> From time to time I seem to find myself needing to create cell
>>>>>> references
>>>>>> between sheets in the same workbook where the source data is stored
>>>>>> 'vertically', yet I need the results to be shown 'horizontally'.
>>>>>>
>>>>>> As a simple example, if Sheet 1 contains data in column A, cells 1 -
>>>>>> 100,
>>>>>> and I want to link this to cells in Sheet 2 in Row 1 (ie; A1, B1, C1,
>>>>>> etc),
>>>>>> how can I do this other than manually typing the cell references each
>>>>>> time?
>>>>>>
>>>>>> On Sheet 2 I need to see - A1: =Sheet1!A1, B1: =Sheet1!A2, etc.
>>>>>>
>>>>>> Thanks!
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>>

>>
>>

>

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1