+ Reply to Thread
Results 1 to 7 of 7

Dragging & incrementing cell values

  1. #1
    Terry Bennett
    Guest

    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. #2
    Elkar
    Guest

    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. #3
    Terry Bennett
    Guest

    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. #4
    Peo Sjoblom
    Guest

    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
    news:OASkd0xWGHA.4692@TK2MSFTNGP04.phx.gbl...
    > 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. #5
    Terry Bennett
    Guest

    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
    > news:OASkd0xWGHA.4692@TK2MSFTNGP04.phx.gbl...
    >> 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. #6
    Peo Sjoblom
    Guest

    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
    >> news:OASkd0xWGHA.4692@TK2MSFTNGP04.phx.gbl...
    >>> 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. #7
    Terry Bennett
    Guest

    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
    >>> news:OASkd0xWGHA.4692@TK2MSFTNGP04.phx.gbl...
    >>>> 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!
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>
    >>>>
    >>>

    >>
    >>

    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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