O.k. please bear with me for this one I will try my best to explain. I am
running a report (layout) using peoplesoft software and it returns the
information in excel. When I run the layout the criteria is entered in one
row, say A4 for example. The problem is that when I run the report (layout)
the information expands. So I now have information in cel A4 - A10 (or
wherever the data ends) on my layout worksheet. I want to link the layout
worksheet to a different worksheet so I can format it, and display it in a
more visually appealing manner. The problem is with the link. Before I run
the report I link up the cell in the worksheet where I wish to display the
information, to A4 in the alyout worksheet. After running the report and
having the information expand it starts the information at cell A10(or where
ever the data stops). I want to know if there is a way to link it or solve
this problem in anyway without a macro. I built a macro that I run after the
report is run, so I can retrieve all the information, but I do not have
enough VBA knowledge to do what I wish. . . . . Keep in mind that the
problem is not linking the data after the report is run, but the problem is
linking the information before the report is run, then running the report,
and having the link move (acting as if it is absolute referenced ($)).
Hopefully this isnt too confusing. Thank you in advance

Good morning Steve,

hope I understood you right, but I think you could connect
the other worksheet with a relational range and create
that range in a varying length.... presumed is that your
information (A4-A10) ends with an empty cell below your
last information-containing cell...

then you could e.g. use

Range("a4", "a" & Range("a4", "a36654").SpecialCells
(xlCellTypeBlanks).Cells(1).Row - 1).Name
= "informationrange"

to create a range named "informationrange" beginning at A4
and ending at the last cell within column a that is not
empty.

You could then use this range for your other work by
addressing it with

Range("informationrange")


Hope that helps...

Best

Markus



-----Original Message-----
O.k. please bear with me for this one I will try my best
to explain. I am
running a report (layout) using peoplesoft software and
it returns the
information in excel. When I run the layout the criteria
is entered in one
row, say A4 for example. The problem is that when I run
the report (layout)
the information expands. So I now have information in
cel A4 - A10 (or
wherever the data ends) on my layout worksheet. I want
to link the layout
worksheet to a different worksheet so I can format it,
and display it in a
more visually appealing manner. The problem is with the
link. Before I run
the report I link up the cell in the worksheet where I
wish to display the
information, to A4 in the alyout worksheet. After
running the report and
having the information expand it starts the information
at cell A10(or where
ever the data stops). I want to know if there is a way
to link it or solve
this problem in anyway without a macro. I built a macro
that I run after the
report is run, so I can retrieve all the information, but
I do not have
enough VBA knowledge to do what I wish. . . . . Keep in
mind that the
problem is not linking the data after the report is run,
but the problem is
linking the information before the report is run, then
running the report,
and having the link move (acting as if it is absolute
referenced ($)).
Hopefully this isnt too confusing. Thank you in advance

.

I'm not quite sure what you were trying to explain to me. Is that a formula
that I put in a cell? If you could try to explain it again it would help
alot! thanks again!

"Markus Scheible" wrote:

Good morning Steve,

hope I understood you right, but I think you could connect
the other worksheet with a relational range and create
that range in a varying length.... presumed is that your
information (A4-A10) ends with an empty cell below your
last information-containing cell...

then you could e.g. use

Range("a4", "a" & Range("a4", "a36654").SpecialCells
(xlCellTypeBlanks).Cells(1).Row - 1).Name
= "informationrange"

to create a range named "informationrange" beginning at A4
and ending at the last cell within column a that is not
empty.

You could then use this range for your other work by
addressing it with

Range("informationrange")


Hope that helps...

Best

Markus



-----Original Message-----
O.k. please bear with me for this one I will try my best
to explain. I am
running a report (layout) using peoplesoft software and
it returns the
information in excel. When I run the layout the criteria
is entered in one
row, say A4 for example. The problem is that when I run
the report (layout)
the information expands. So I now have information in
cel A4 - A10 (or
wherever the data ends) on my layout worksheet. I want
to link the layout
worksheet to a different worksheet so I can format it,
and display it in a
more visually appealing manner. The problem is with the
link. Before I run
the report I link up the cell in the worksheet where I
wish to display the
information, to A4 in the alyout worksheet. After
running the report and
having the information expand it starts the information
at cell A10(or where
ever the data stops). I want to know if there is a way
to link it or solve
this problem in anyway without a macro. I built a macro
that I run after the
report is run, so I can retrieve all the information, but
I do not have
enough VBA knowledge to do what I wish. . . . . Keep in
mind that the
problem is not linking the data after the report is run,
but the problem is
linking the information before the report is run, then
running the report,
and having the link move (acting as if it is absolute
referenced ($)).
Hopefully this isnt too confusing. Thank you in advance

.