Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas not working | Excel Discussion (Misc queries) | |||
how to copy formulas to other cells in excell | Setting up and Configuration of Excel | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
Tricky formulas needed | Excel Worksheet Functions |