| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Equation to refer to data value(s) and not the cell?? | Excel Discussion (Misc queries) | |||
| How do I copy cell values (derived from formula), not references? | Excel Worksheet Functions | |||
| VLookup to sum cell values | Excel Worksheet Functions | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
| AutoFIll -by dragging the bottom-right corner of the cell | Excel Discussion (Misc queries) | |||
Linear Mode
