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!

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!
.

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!

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!
.

.