| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Fill to copy from other sheets
I am copying data from one Excel sheet to another. In the source sheet the
data I want to copy does not reside in sequential lines. Instead it occurs on every 6th line i.e. X5, X11, X17 etc... I want to populate a column in the destination sheet solely with data from every 6th line - so I have used "=" and then clicked the source cell on the other sheet three times to get a "sequence" started. I then select all three cells on the destination sheet and drag this down to try to create a full column following the same sequence... this is where something strange happens... the source cell numbers "reset" depending on the address of the starting cell - I am faced with a hugely laborious task copying over hundreds of numbers - any ideas??? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using Fill to copy from other sheets
Hi,
Try this =INDEX(Sheet2!X:X,(ROW(A1)-1)*6+5) drag down as required "Rogerxwilliams" wrote: I am copying data from one Excel sheet to another. In the source sheet the data I want to copy does not reside in sequential lines. Instead it occurs on every 6th line i.e. X5, X11, X17 etc... I want to populate a column in the destination sheet solely with data from every 6th line - so I have used "=" and then clicked the source cell on the other sheet three times to get a "sequence" started. I then select all three cells on the destination sheet and drag this down to try to create a full column following the same sequence... this is where something strange happens... the source cell numbers "reset" depending on the address of the starting cell - I am faced with a hugely laborious task copying over hundreds of numbers - any ideas??? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using Fill to copy from other sheets
not understanding this and cant get it to work
"Mike H" wrote: Hi, Try this =INDEX(Sheet2!X:X,(ROW(A1)-1)*6+5) drag down as required "Rogerxwilliams" wrote: I am copying data from one Excel sheet to another. In the source sheet the data I want to copy does not reside in sequential lines. Instead it occurs on every 6th line i.e. X5, X11, X17 etc... I want to populate a column in the destination sheet solely with data from every 6th line - so I have used "=" and then clicked the source cell on the other sheet three times to get a "sequence" started. I then select all three cells on the destination sheet and drag this down to try to create a full column following the same sequence... this is where something strange happens... the source cell numbers "reset" depending on the address of the starting cell - I am faced with a hugely laborious task copying over hundreds of numbers - any ideas??? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using Fill to copy from other sheets
Hi,
Put the formula anywhere except column X of sheet 2 and it will return the contents of Sheet 2 - X5. Drag down for Sheet 2 - X11 etc. Mike "Rogerxwilliams" wrote: not understanding this and cant get it to work "Mike H" wrote: Hi, Try this =INDEX(Sheet2!X:X,(ROW(A1)-1)*6+5) drag down as required "Rogerxwilliams" wrote: I am copying data from one Excel sheet to another. In the source sheet the data I want to copy does not reside in sequential lines. Instead it occurs on every 6th line i.e. X5, X11, X17 etc... I want to populate a column in the destination sheet solely with data from every 6th line - so I have used "=" and then clicked the source cell on the other sheet three times to get a "sequence" started. I then select all three cells on the destination sheet and drag this down to try to create a full column following the same sequence... this is where something strange happens... the source cell numbers "reset" depending on the address of the starting cell - I am faced with a hugely laborious task copying over hundreds of numbers - any ideas??? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using Fill to copy from other sheets
Hi Mike
Possible misunderstanding. My source data is in sheet 1 in cells X5 (then) X11 (then) X17 etc through to "hundreds" of entries. I am trying to copy these cells "only" into a sequential list on another sheet (e.g. C1, C2, C3 etc... so that I can total them up) Using your approach I am still getting strnage combinations / sequences of data. Appreciate the help "Mike H" wrote: Hi, Put the formula anywhere except column X of sheet 2 and it will return the contents of Sheet 2 - X5. Drag down for Sheet 2 - X11 etc. Mike "Rogerxwilliams" wrote: not understanding this and cant get it to work "Mike H" wrote: Hi, Try this =INDEX(Sheet2!X:X,(ROW(A1)-1)*6+5) drag down as required "Rogerxwilliams" wrote: I am copying data from one Excel sheet to another. In the source sheet the data I want to copy does not reside in sequential lines. Instead it occurs on every 6th line i.e. X5, X11, X17 etc... I want to populate a column in the destination sheet solely with data from every 6th line - so I have used "=" and then clicked the source cell on the other sheet three times to get a "sequence" started. I then select all three cells on the destination sheet and drag this down to try to create a full column following the same sequence... this is where something strange happens... the source cell numbers "reset" depending on the address of the starting cell - I am faced with a hugely laborious task copying over hundreds of numbers - any ideas??? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using Fill to copy from other sheets
Hi
Possible misunderstanding. My source data is in sheet 1 in cells X5 (then) X11 (then) X17 etc through to "hundreds" of entries. So the formula in C1 and any sheet becomes =INDEX(Sheet1!X:X,(ROW(A1)-1)*6+5) it will return the contents of X5 on sheet 1. Drag down into c2 and it will return X11 of sheet 1 Mike "Rogerxwilliams" wrote: Hi Mike Possible misunderstanding. My source data is in sheet 1 in cells X5 (then) X11 (then) X17 etc through to "hundreds" of entries. I am trying to copy these cells "only" into a sequential list on another sheet (e.g. C1, C2, C3 etc... so that I can total them up) Using your approach I am still getting strnage combinations / sequences of data. Appreciate the help "Mike H" wrote: Hi, Put the formula anywhere except column X of sheet 2 and it will return the contents of Sheet 2 - X5. Drag down for Sheet 2 - X11 etc. Mike "Rogerxwilliams" wrote: not understanding this and cant get it to work "Mike H" wrote: Hi, Try this =INDEX(Sheet2!X:X,(ROW(A1)-1)*6+5) drag down as required "Rogerxwilliams" wrote: I am copying data from one Excel sheet to another. In the source sheet the data I want to copy does not reside in sequential lines. Instead it occurs on every 6th line i.e. X5, X11, X17 etc... I want to populate a column in the destination sheet solely with data from every 6th line - so I have used "=" and then clicked the source cell on the other sheet three times to get a "sequence" started. I then select all three cells on the destination sheet and drag this down to try to create a full column following the same sequence... this is where something strange happens... the source cell numbers "reset" depending on the address of the starting cell - I am faced with a hugely laborious task copying over hundreds of numbers - any ideas??? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using Fill to copy from other sheets
Oh... wait - I've got it now fab - works a treat - God knows how!
You are a Genius "Mike H" wrote: Hi, Put the formula anywhere except column X of sheet 2 and it will return the contents of Sheet 2 - X5. Drag down for Sheet 2 - X11 etc. Mike "Rogerxwilliams" wrote: not understanding this and cant get it to work "Mike H" wrote: Hi, Try this =INDEX(Sheet2!X:X,(ROW(A1)-1)*6+5) drag down as required "Rogerxwilliams" wrote: I am copying data from one Excel sheet to another. In the source sheet the data I want to copy does not reside in sequential lines. Instead it occurs on every 6th line i.e. X5, X11, X17 etc... I want to populate a column in the destination sheet solely with data from every 6th line - so I have used "=" and then clicked the source cell on the other sheet three times to get a "sequence" started. I then select all three cells on the destination sheet and drag this down to try to create a full column following the same sequence... this is where something strange happens... the source cell numbers "reset" depending on the address of the starting cell - I am faced with a hugely laborious task copying over hundreds of numbers - any ideas??? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Drag and fill from other sheets | Excel Discussion (Misc queries) | |||
| source sheet to auto fill 2 other sheets | New Users to Excel | |||
| move or copy sheets doesn't copy format | Excel Worksheet Functions | |||
| The fill feature in Excel that gives option to fill or copy | New Users to Excel | |||
| in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
Hybrid Mode
