Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to move data on worksheet
Hi there. Using Office 02 on XP. Usually haunt the Access
Newsgroup area. My new Access project will require importing data from Excel and I want to be sure I start off on the right foot. I have imported data before but this case is odd. The Excel file has one worksheet and basically contains columns of funds with rows of various return calculations (1 yr, 3 yr, 5 yr, etc.). All cells contain values, not formulas. My problem is that the data is 'stacked' downwards rather than just going on out to the right. Here is an example: A| B| C| D| E| 1|Fund Omni Bond MnMt Intn 2|Date 02/15 02/15 02/15 02/15 3|1Yr -1.08% 3.54%-12.55% 2.31% 4|3Yr 4.65% 8.55% 8.65% -6.42% 5| 6|Fund Fdly Drfs RBSN IBOP 7|Date 02/15 02/15 02/15 02/15 8|1Yr -1.08% 3.54%-12.55% 2.31% 9|3Yr 4.65% 8.55% 8.65% -6.42% The sheets have 4 tiers of columns with 17 funds (Columns E-U)(the 3rd tier has 18 funds to Col V). There are rates of return on rows 12, 17-20, 23, 25-27 for tier 1; rows 38, 43-46, 49, 51-53 for tier 2; etc. Is it better to reposition tiers 2-4 so I have one tier only or can I/should I 'program' the import with certain cell addresses? Can the repositioning be done by command or must it be done manually? If by command, can you give me an overview or direct me to one? It needs to be able to be imported into Access. I'm not a programmer but do a little VB and event procedure stuff in Access. Would appreciate very much any help or advice. Thanks in advance for your time! |
#2
|
|||
|
|||
hi,
What i would do is make it all one tier, give it a range name then link to it instead of importing. if this is a one time thing i would just do it manully. looks like a simple copy/paste. writing code may take longer. -----Original Message----- Hi there. Using Office 02 on XP. Usually haunt the Access Newsgroup area. My new Access project will require importing data from Excel and I want to be sure I start off on the right foot. I have imported data before but this case is odd. The Excel file has one worksheet and basically contains columns of funds with rows of various return calculations (1 yr, 3 yr, 5 yr, etc.). All cells contain values, not formulas. My problem is that the data is 'stacked' downwards rather than just going on out to the right. Here is an example: A| B| C| D| E| 1|Fund Omni Bond MnMt Intn 2|Date 02/15 02/15 02/15 02/15 3|1Yr -1.08% 3.54%-12.55% 2.31% 4|3Yr 4.65% 8.55% 8.65% -6.42% 5| 6|Fund Fdly Drfs RBSN IBOP 7|Date 02/15 02/15 02/15 02/15 8|1Yr -1.08% 3.54%-12.55% 2.31% 9|3Yr 4.65% 8.55% 8.65% -6.42% The sheets have 4 tiers of columns with 17 funds (Columns E-U)(the 3rd tier has 18 funds to Col V). There are rates of return on rows 12, 17-20, 23, 25-27 for tier 1; rows 38, 43-46, 49, 51-53 for tier 2; etc. Is it better to reposition tiers 2-4 so I have one tier only or can I/should I 'program' the import with certain cell addresses? Can the repositioning be done by command or must it be done manually? If by command, can you give me an overview or direct me to one? It needs to be able to be imported into Access. I'm not a programmer but do a little VB and event procedure stuff in Access. Would appreciate very much any help or advice. Thanks in advance for your time! . |
#3
|
|||
|
|||
Hi Bonnie-
IMHO, based on how I would want my Access table defined, I would also restructure the worksheet so that the Fund Names were in a column with the date & rates as additional "field" columns. Listing all funds in consecutive rows as Records should make the import a snap. |:) "Bonnie" wrote: Hi there. Using Office 02 on XP. Usually haunt the Access Newsgroup area. My new Access project will require importing data from Excel and I want to be sure I start off on the right foot. I have imported data before but this case is odd. The Excel file has one worksheet and basically contains columns of funds with rows of various return calculations (1 yr, 3 yr, 5 yr, etc.). All cells contain values, not formulas. My problem is that the data is 'stacked' downwards rather than just going on out to the right. Here is an example: A| B| C| D| E| 1|Fund Omni Bond MnMt Intn 2|Date 02/15 02/15 02/15 02/15 3|1Yr -1.08% 3.54%-12.55% 2.31% 4|3Yr 4.65% 8.55% 8.65% -6.42% 5| 6|Fund Fdly Drfs RBSN IBOP 7|Date 02/15 02/15 02/15 02/15 8|1Yr -1.08% 3.54%-12.55% 2.31% 9|3Yr 4.65% 8.55% 8.65% -6.42% The sheets have 4 tiers of columns with 17 funds (Columns E-U)(the 3rd tier has 18 funds to Col V). There are rates of return on rows 12, 17-20, 23, 25-27 for tier 1; rows 38, 43-46, 49, 51-53 for tier 2; etc. Is it better to reposition tiers 2-4 so I have one tier only or can I/should I 'program' the import with certain cell addresses? Can the repositioning be done by command or must it be done manually? If by command, can you give me an overview or direct me to one? It needs to be able to be imported into Access. I'm not a programmer but do a little VB and event procedure stuff in Access. Would appreciate very much any help or advice. Thanks in advance for your time! |
#4
|
|||
|
|||
Hello Anonymous,
Thanks for the info. This project will replace someone keying in 700-1000 numbers every month so not a one time thing. The sheet will not vary. I would like to know is it possible to position my cursor in, say, Cell D35 and have a macro or code then highlight Cell D35 down/over to U51. That would make the copy/paste much smoother. Appreciate your time and any advice or help. -----Original Message----- hi, What i would do is make it all one tier, give it a range name then link to it instead of importing. if this is a one time thing i would just do it manully. looks like a simple copy/paste. writing code may take longer. -----Original Message----- Hi there. Using Office 02 on XP. Usually haunt the Access Newsgroup area. My new Access project will require importing data from Excel and I want to be sure I start off on the right foot. I have imported data before but this case is odd. The Excel file has one worksheet and basically contains columns of funds with rows of various return calculations (1 yr, 3 yr, 5 yr, etc.). All cells contain values, not formulas. My problem is that the data is 'stacked' downwards rather than just going on out to the right. Here is an example: A| B| C| D| E| 1|Fund Omni Bond MnMt Intn 2|Date 02/15 02/15 02/15 02/15 3|1Yr -1.08% 3.54%-12.55% 2.31% 4|3Yr 4.65% 8.55% 8.65% -6.42% 5| 6|Fund Fdly Drfs RBSN IBOP 7|Date 02/15 02/15 02/15 02/15 8|1Yr -1.08% 3.54%-12.55% 2.31% 9|3Yr 4.65% 8.55% 8.65% -6.42% The sheets have 4 tiers of columns with 17 funds (Columns E-U)(the 3rd tier has 18 funds to Col V). There are rates of return on rows 12, 17-20, 23, 25-27 for tier 1; rows 38, 43-46, 49, 51-53 for tier 2; etc. Is it better to reposition tiers 2-4 so I have one tier only or can I/should I 'program' the import with certain cell addresses? Can the repositioning be done by command or must it be done manually? If by command, can you give me an overview or direct me to one? It needs to be able to be imported into Access. I'm not a programmer but do a little VB and event procedure stuff in Access. Would appreciate very much any help or advice. Thanks in advance for your time! . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract data from worksheet | Excel Worksheet Functions | |||
How do I collect data from an interactive web worksheet and impor. | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Reference Data in Moved Worksheet | Setting up and Configuration of Excel |