Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfer data from sheet1 to sheet 2
Hi, i'm having the hardest time trying to transfer data from one sheet
to another sheet. The data that I receive comes from different sources and are always in different order, however, the column name is always the same. I wanted to transfer only the columns that I need to another sheet within the same workbook in a specified order. In other words, I want to make a template so that each time I copy and paste the data that I received into sheet 1, I want the columns to be automatically transferred to sheet 2 in the specified order. It doesn't matter if its in excel formatting or vba, but if only availble in vba format, then very clear descriptions will be greatly appreciated. I would be very thankful for any help on this matter. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfer data from sheet1 to sheet 2
"joe311" wrote:
Hi, i'm having the hardest time trying to transfer data from one sheet to another sheet. The data that I receive comes from different sources and are always in different order, however, the column name is always the same. I wanted to transfer only the columns that I need to another sheet within the same workbook in a specified order. In other words, I want to make a template so that each time I copy and paste the data that I received into sheet 1, I want the columns to be automatically transferred to sheet 2 in the specified order. It doesn't matter if its in excel formatting or vba, but if only availble in vba format, then very clear descriptions will be greatly appreciated. I would be very thankful for any help on this matter. One play which automates it using formulas ... Assuming source data in Sheet1, cols A to C, col headers in row1, data from row2 down Field1 Field2 Field3 data1 data2 data3 data11 data22 data33 data12 data23 data34 etc And in Sheet2, Assume the desired sequence of the col headers placed in A1:C1 a Field3, Field1, Field2 Put in A2: =OFFSET(Sheet1!$A$1,ROW(A1),MATCH(A$1,Sheet1!$1:$1 ,0)-1) Copy A2 to C2, fill down to say, C100, to cover the max expected extent of data in Sheet1 For a cleaner look, suppress extraneous zeros in the sheet via: Click Tools Options View tab Uncheck "Zero values" OK The above will return the required results in Sheet2 To refresh the data in Sheet1, clear it with the Delete key, then copypaste (or paste special values) the new data Do *not* clear by deleting the cols in Sheet1, as this would foul up the formulas in Sheet2 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfer data from sheet1 to sheet 2
thanks max for the help, but it's not exactly what i was looking for, i
dont think i was clear enough about what i needed. i get data sheets from different people, they are always in a different order, but have the same heading for each column. i wanted to pull the data from the different people and make a separate sheet that automatically puts the columns of data in the order i want it. for example. on monday i receive a data sheet with the following columns... id# name address phone# car birthday on tuesday i receive a data sheet with the following columns... birthday name address car phone# id# what i want is a template that after i input the given data would place all the information into an order i need it to be in... such as... id# birthday name phone# address car |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfer data from sheet1 to sheet 2
"joe311" wrote:
thanks max for the help, but it's not exactly what i was looking for, i dont think i was clear enough about what i needed. i get data sheets from different people, they are always in a different order, but have the same heading for each column. i wanted to pull the data from the different people and make a separate sheet that automatically puts the columns of data in the order i want it. for example. on monday i receive a data sheet with the following columns... id# name address phone# car birthday on tuesday i receive a data sheet with the following columns... birthday name address car phone# id# what i want is a template that after i input the given data would place all the information into an order i need it to be in... such as... id# birthday name phone# address car Think my earlier interp was'nt far off, and the suggestion should have worked ?? You just need to define the order of the column headers you want in Sheet2, and refresh the source data in Sheet1 (I used as examples of the column headers: Field1, Field2, Field3) Sheet2 would be the "template" that auto-returns the results based on the source data that's pasted/refreshed into Sheet1 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfer data from sheet1 to sheet 2
ok...got it...it works..thank you very much!
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfer data from sheet1 to sheet 2
"joe311" wrote:
ok...got it...it works..thank you very much! Glad to hear that ! Thanks for feeding back .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I transfer data from 1 sheet to another? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Append the data given in diff sheets of an Excel File to one sheet | Excel Worksheet Functions | |||
Automatic cell increment with data from sheet 1 to sheet 2 | Excel Worksheet Functions | |||
can I transfer data from sheet 1 to sheet2? | Excel Discussion (Misc queries) |