Autofill Formulae

[Guest]

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

 

[oferns]

Hi,
can you use TRANSPOSE() function and Array enter (Ctrl+Shift+Enter) it?
OJ

 

[Max]

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".

 

[oferns]

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

 

[oferns]

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

 

[Max]

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".