I have several workbooks (approx. 33) that contain data that I want to see in
a seperate workbook. I have created formulas that can bring the data forward.
The problems I am having is that the names of the workbooks change monthly.
I have to change every cell to reflect the different name. Is there a way
in the formula that will find the workbook without having to change every
cell.
AND
I am getting data from a certain column. Again, I have to change every cell
to get the data from that particular column. There has to be a simple, less
time consuming way to retrive the data.

If you want to open all the workbooks, you can use the indirect function to
build your references. It doesn't work with closed workbooks.

--
Regards,
Tom Ogilvy

"sir lancelot" wrote in message
...
I have several workbooks (approx. 33) that contain data that I want to see
in
a seperate workbook. I have created formulas that can bring the data
forward.
The problems I am having is that the names of the workbooks change
monthly.
I have to change every cell to reflect the different name. Is there a way
in the formula that will find the workbook without having to change every
cell.
AND
I am getting data from a certain column. Again, I have to change every
cell
to get the data from that particular column. There has to be a simple,
less
time consuming way to retrive the data.

I am not interested in opening the workbooks. I am trying to receive Certain
rows and columns of information from all the workbooks and generating that
information to only one workbook without having to change the formulas in
each and every cell.

"Tom Ogilvy" wrote:

If you want to open all the workbooks, you can use the indirect function to
build your references. It doesn't work with closed workbooks.

--
Regards,
Tom Ogilvy

"sir lancelot" wrote in message
...
I have several workbooks (approx. 33) that contain data that I want to see
in
a seperate workbook. I have created formulas that can bring the data
forward.
The problems I am having is that the names of the workbooks change
monthly.
I have to change every cell to reflect the different name. Is there a way
in the formula that will find the workbook without having to change every
cell.
AND
I am getting data from a certain column. Again, I have to change every
cell
to get the data from that particular column. There has to be a simple,
less
time consuming way to retrive the data.

"I am not interested in opening the workbooks." Well, there goes the easy
solution.

Guess then you would have to write a macro to make the changes for you.

--
Regards,
Tom Ogilvy


"sir lancelot" wrote in message
...
I am not interested in opening the workbooks. I am trying to receive
Certain
rows and columns of information from all the workbooks and generating that
information to only one workbook without having to change the formulas in
each and every cell.

"Tom Ogilvy" wrote:

If you want to open all the workbooks, you can use the indirect function
to
build your references. It doesn't work with closed workbooks.

--
Regards,
Tom Ogilvy

"sir lancelot" wrote in message
...
I have several workbooks (approx. 33) that contain data that I want to
see
in
a seperate workbook. I have created formulas that can bring the data
forward.
The problems I am having is that the names of the workbooks change
monthly.
I have to change every cell to reflect the different name. Is there a
way
in the formula that will find the workbook without having to change
every
cell.
AND
I am getting data from a certain column. Again, I have to change
every
cell
to get the data from that particular column. There has to be a
simple,
less
time consuming way to retrive the data.

I do something similar and use vb to solve. You can also set up cells that
when concatenated point to the correct worksheets/columns. Then you copy,
paste special, values and viola, you have your link formula. Then you copy
the formula down or accross. My filenames change per date so it is easy to
set up a cell formula to come up with the correct filename:
="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" You
can link the Today() to a cell with the date you want and also have another
cell that contains the correct column, etc.

Hope that helps

dave


"sir lancelot" wrote in message
...
I have several workbooks (approx. 33) that contain data that I want to see
in
a seperate workbook. I have created formulas that can bring the data
forward.
The problems I am having is that the names of the workbooks change
monthly.
I have to change every cell to reflect the different name. Is there a way
in the formula that will find the workbook without having to change every
cell.
AND
I am getting data from a certain column. Again, I have to change every
cell
to get the data from that particular column. There has to be a simple,
less
time consuming way to retrive the data.

thank you, this made it easier.

"Dave" wrote:

I do something similar and use vb to solve. You can also set up cells that
when concatenated point to the correct worksheets/columns. Then you copy,
paste special, values and viola, you have your link formula. Then you copy
the formula down or accross. My filenames change per date so it is easy to
set up a cell formula to come up with the correct filename:
="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1" You
can link the Today() to a cell with the date you want and also have another
cell that contains the correct column, etc.

Hope that helps

dave


"sir lancelot" wrote in message
...
I have several workbooks (approx. 33) that contain data that I want to see
in
a seperate workbook. I have created formulas that can bring the data
forward.
The problems I am having is that the names of the workbooks change
monthly.
I have to change every cell to reflect the different name. Is there a way
in the formula that will find the workbook without having to change every
cell.
AND
I am getting data from a certain column. Again, I have to change every
cell
to get the data from that particular column. There has to be a simple,
less
time consuming way to retrive the data.