Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Autofill Formulae
Hoping someone can help me to solve my dilemma.
My spreadsheet has two worksheets. One worksheet has a list of numeric values running vertically, and I am trying to transplant them onto the master worksheet running horizontally. eg on the secondary worksheet A1=5, A2=6, A3=8 etc on the master sheet, A1 ='secondaryworksheet'A1 and B1 ='secondaryworksheet'A2 etc The problem comes when I try to autofill across on the master spreadsheet. Excel does not seem to recognise the sequence (I think it may be because of the horizontal/vertical factor). I have tried to use absolute reference to lock the column letter, but the numbers still do not go up in sequence. Due to the size of the spreadsheet (5mb so far - so a lot of data) it is not practical to manually fix all of the formulae. Does any have a suggestion that might help? Thanks |
#2
|
|||
|
|||
Hi,
can you use TRANSPOSE() function and Array enter (Ctrl+Shift+Enter) it? OJ |
#3
|
|||
|
|||
One way
In the master sheet: Put in A1: =OFFSET(secondaryworksheet!$A$1,COLUMNS($A$1:A1)-1,ROWS($A$1:A1)-1) Copy A1 across as many cols as you have rows to extract from "secondaryworksheet". -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "system_d" wrote in message ... Hoping someone can help me to solve my dilemma. My spreadsheet has two worksheets. One worksheet has a list of numeric values running vertically, and I am trying to transplant them onto the master worksheet running horizontally. eg on the secondary worksheet A1=5, A2=6, A3=8 etc on the master sheet, A1 ='secondaryworksheet'A1 and B1 ='secondaryworksheet'A2 etc The problem comes when I try to autofill across on the master spreadsheet. Excel does not seem to recognise the sequence (I think it may be because of the horizontal/vertical factor). I have tried to use absolute reference to lock the column letter, but the numbers still do not go up in sequence. Due to the size of the spreadsheet (5mb so far - so a lot of data) it is not practical to manually fix all of the formulae. Does any have a suggestion that might help? Thanks |
#4
|
|||
|
|||
....if not this formula will do it...
=OFFSET(Sheet1!$A$1,COLUMN(),0) Copy it across the columns you want replace where Sheet1 is the Secondary sheet. OJ |
#5
|
|||
|
|||
....if not this formula will do it...
=OFFSET(Sheet1!$A$1,COLUMN(),0) Copy it across the columns you want replace where Sheet1 is the Secondary sheet. OJ |
#6
|
|||
|
|||
Oops, think the formula given* was an overkill <g
This shorter one suffices for the purpose: Put in A1: =OFFSET(secondaryworksheet!$A$1,COLUMNS($A$1:A1)-1,) Copy A1 across as many cols as you have rows to extract from "secondaryworksheet". *It's meant for copying across and down to transpose what's in "secondaryworksheet". -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
autofill macro | Excel Discussion (Misc queries) | |||
cell height changing when using autofill | Excel Discussion (Misc queries) | |||
How do I autofill combo boxes with their destination cell? | Excel Worksheet Functions | |||
vlookup change column index position - without changing formulae | Excel Worksheet Functions | |||
Autofill skipping cells | Excel Worksheet Functions |