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!

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

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

"Elkar" <[email protected]> wrote in message
news:[email protected]...
> 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!
>>
>>
>>

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" <[email protected]> wrote in message
news:[email protected]...
> That just gives me a load of #REF! error messages.
>
> "Elkar" <[email protected]> wrote in message
> news:[email protected]...
>> 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!
>>>
>>>
>>>
>
>

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

"Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> That just gives me a load of #REF! error messages.
>>
>> "Elkar" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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!
>>>>
>>>>
>>>>
>>
>>
>

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" <[email protected]> wrote in message
news:[email protected]...
> Looks like the same as Elkar suggested which didn't work?
>
> "Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> That just gives me a load of #REF! error messages.
>>>
>>> "Elkar" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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!
>>>>>
>>>>>
>>>>>
>>>
>>>
>>
>
>

Thanks very much - that seems to work fine.

"Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:O%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> Looks like the same as Elkar suggested which didn't work?
>>
>> "Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> That just gives me a load of #REF! error messages.
>>>>
>>>> "Elkar" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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!
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>>
>>
>>
>