Dragging formulas
 

Hope someone can help out here, please...
Excel 2003:
I have a list of columns (i.e. B8, C8, D8, E8 etc) that I want to copy down
a row on another sheet.

For example, on my other sheet, in cells D4 to D14, I want it to reference
cells across the colums, going, B8, C8, D8 etc. I've tried using absolutes
($) and still when I drag down the Autofill, it changes the row rather than
the column, even if i give it 10 cells of data I have manually entered to
give it the idea.

I'd type them in myself if i didn't have 8 columns of over 350 rows to enter!

Any ideas?

Ben

Hi
in D4 enter
=OFFSET('sheet1'!$B$8,0,ROW(1:1)-1)
and copy this down

"Ben Greenwood" wrote:

Hope someone can help out here, please...
Excel 2003:
I have a list of columns (i.e. B8, C8, D8, E8 etc) that I want to copy down
a row on another sheet.

For example, on my other sheet, in cells D4 to D14, I want it to reference
cells across the colums, going, B8, C8, D8 etc. I've tried using absolutes
($) and still when I drag down the Autofill, it changes the row rather than
the column, even if i give it 10 cells of data I have manually entered to
give it the idea.

I'd type them in myself if i didn't have 8 columns of over 350 rows to enter!

Any ideas?

Ben

Select the range where you want the new formula (D4 to D14)
Then on cell D4 type
=transpose(Sheet1!B8:I8)
and array enter it (CTRL + Shift + Enter instead of hitting just Enter)


"Ben Greenwood" wrote in message
...
Hope someone can help out here, please...
Excel 2003:
I have a list of columns (i.e. B8, C8, D8, E8 etc) that I want to copy
down
a row on another sheet.

For example, on my other sheet, in cells D4 to D14, I want it to reference
cells across the colums, going, B8, C8, D8 etc. I've tried using absolutes
($) and still when I drag down the Autofill, it changes the row rather
than
the column, even if i give it 10 cells of data I have manually entered to
give it the idea.

I'd type them in myself if i didn't have 8 columns of over 350 rows to
enter!

Any ideas?

Ben

Brilliant! Thanks, Farnk, you've saved me a lot of work!

"Frank Kabel" wrote:

Hi
in D4 enter
=OFFSET('sheet1'!$B$8,0,ROW(1:1)-1)
and copy this down

"Ben Greenwood" wrote:

Hope someone can help out here, please...
Excel 2003:
I have a list of columns (i.e. B8, C8, D8, E8 etc) that I want to copy down
a row on another sheet.

For example, on my other sheet, in cells D4 to D14, I want it to reference
cells across the colums, going, B8, C8, D8 etc. I've tried using absolutes
($) and still when I drag down the Autofill, it changes the row rather than
the column, even if i give it 10 cells of data I have manually entered to
give it the idea.

I'd type them in myself if i didn't have 8 columns of over 350 rows to enter!

Any ideas?

Ben

Dragging formulas
 

Frank,

I would like to do this exact same thing only reversed - i.e. copy data that
is going down by rows across columns. I tried reversing the function using
the "column" function, but it does not seem to be working.

Can you help?

Thanks,

Chaim

"Frank Kabel" wrote:

Hi
in D4 enter
=OFFSET('sheet1'!$B$8,0,ROW(1:1)-1)
and copy this down

"Ben Greenwood" wrote:

Hope someone can help out here, please...
Excel 2003:
I have a list of columns (i.e. B8, C8, D8, E8 etc) that I want to copy down
a row on another sheet.

For example, on my other sheet, in cells D4 to D14, I want it to reference
cells across the colums, going, B8, C8, D8 etc. I've tried using absolutes
($) and still when I drag down the Autofill, it changes the row rather than
the column, even if i give it 10 cells of data I have manually entered to
give it the idea.

I'd type them in myself if i didn't have 8 columns of over 350 rows to enter!

Any ideas?

Ben

Dragging formulas
 

I think I figured it out

all I have to do is use

=offset(reference fo my cell,column(A:A)-1,0)

is that right, or is there a better way?

Thanks!

"Frank Kabel" wrote:

Hi
in D4 enter
=OFFSET('sheet1'!$B$8,0,ROW(1:1)-1)
and copy this down

"Ben Greenwood" wrote:

Hope someone can help out here, please...
Excel 2003:
I have a list of columns (i.e. B8, C8, D8, E8 etc) that I want to copy down
a row on another sheet.

For example, on my other sheet, in cells D4 to D14, I want it to reference
cells across the colums, going, B8, C8, D8 etc. I've tried using absolutes
($) and still when I drag down the Autofill, it changes the row rather than
the column, even if i give it 10 cells of data I have manually entered to
give it the idea.

I'd type them in myself if i didn't have 8 columns of over 350 rows to enter!

Any ideas?

Ben

Dragging formulas
 

Unfortunately Frank Kabel died in a car accident in January 2005, here's an
adaptation


=OFFSET(Sheet1!$B$8,COLUMNS($A$1:A1)-1,)

copied across will pull the same way


a non volatile version might look like

=INDEX(Sheet1!$B$8:$B$65535,COLUMNS($A$1:A1))


Replace B1 with the first cell in the range you want to pull from and B65535
with the last cell


--
Regards,

Peo Sjoblom




"clubin" wrote in message
...
Frank,

I would like to do this exact same thing only reversed - i.e. copy data
that
is going down by rows across columns. I tried reversing the function
using
the "column" function, but it does not seem to be working.

Can you help?

Thanks,

Chaim

"Frank Kabel" wrote:

Hi
in D4 enter
=OFFSET('sheet1'!$B$8,0,ROW(1:1)-1)
and copy this down

"Ben Greenwood" wrote:

Hope someone can help out here, please...
Excel 2003:
I have a list of columns (i.e. B8, C8, D8, E8 etc) that I want to copy
down
a row on another sheet.

For example, on my other sheet, in cells D4 to D14, I want it to
reference
cells across the colums, going, B8, C8, D8 etc. I've tried using
absolutes
($) and still when I drag down the Autofill, it changes the row rather
than
the column, even if i give it 10 cells of data I have manually entered
to
give it the idea.

I'd type them in myself if i didn't have 8 columns of over 350 rows to
enter!

Any ideas?

Ben

Dragging formulas
 

Sorry to hear about Frank, my sympathies.

Thank you for your help

"Peo Sjoblom" wrote:

Unfortunately Frank Kabel died in a car accident in January 2005, here's an
adaptation


=OFFSET(Sheet1!$B$8,COLUMNS($A$1:A1)-1,)

copied across will pull the same way


a non volatile version might look like

=INDEX(Sheet1!$B$8:$B$65535,COLUMNS($A$1:A1))


Replace B1 with the first cell in the range you want to pull from and B65535
with the last cell


--
Regards,

Peo Sjoblom




"clubin" wrote in message
...
Frank,

I would like to do this exact same thing only reversed - i.e. copy data
that
is going down by rows across columns. I tried reversing the function
using
the "column" function, but it does not seem to be working.

Can you help?

Thanks,

Chaim

"Frank Kabel" wrote:

Hi
in D4 enter
=OFFSET('sheet1'!$B$8,0,ROW(1:1)-1)
and copy this down

"Ben Greenwood" wrote:

Hope someone can help out here, please...
Excel 2003:
I have a list of columns (i.e. B8, C8, D8, E8 etc) that I want to copy
down
a row on another sheet.

For example, on my other sheet, in cells D4 to D14, I want it to
reference
cells across the colums, going, B8, C8, D8 etc. I've tried using
absolutes
($) and still when I drag down the Autofill, it changes the row rather
than
the column, even if i give it 10 cells of data I have manually entered
to
give it the idea.

I'd type them in myself if i didn't have 8 columns of over 350 rows to
enter!

Any ideas?

Ben