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!

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!

Dragging & incrementing cell values
 

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

"Elkar" wrote in message
...
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!

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" wrote in message
...
That just gives me a load of #REF! error messages. :(

"Elkar" wrote in message
...
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!

Dragging & incrementing cell values
 

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

"Peo Sjoblom" wrote in message
...
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" wrote in message
...
That just gives me a load of #REF! error messages. :(

"Elkar" wrote in message
...
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!

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" wrote in message
...
Looks like the same as Elkar suggested which didn't work?

"Peo Sjoblom" wrote in message
...
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" wrote in message
...
That just gives me a load of #REF! error messages. :(

"Elkar" wrote in message
...
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!

Dragging & incrementing cell values
 

Thanks very much - that seems to work fine.

"Peo Sjoblom" wrote in message
...
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" wrote in message
...
Looks like the same as Elkar suggested which didn't work?

"Peo Sjoblom" wrote in message
...
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" wrote in message
...
That just gives me a load of #REF! error messages. :(

"Elkar" wrote in message
...
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!