another cell linking question

B

bredar

Hello,
I am very new to this but have most of what I need thanks to searchin
this site.
I will have two workbooks open. The source workbook name will chang
everyday so I created the link indirectly in the destination workboo
and for now will require the user to enter the name in a cell. Thi
will work fine for now and will be easy for them because it is a dat
code.
Anyway, I have the following formula in a cell in the destinatio
workbook:
=INDIRECT("'[" & D1 &".CSV]" & D1 & "'!$A$1")

D1 contains the file name which is just a .csv file of data that i
created by a PLC with a new file name at midnight to create reasonabl
size files to view.
The above formula works for me. However, I need to go a little furthe
and am now stuck. I do not want just cell A1 to be linked, I want th
whole sheet of data copied over. And anything I've tried to resembl
what intuitively makes sense is not working or what I get when I try t
select the cells with the formula active and let it fill itself in (suc
as
!$A$1:$B$3 or !$1:$65536, etc.)
Let me know if you need more details and thanks
 
B

bredar

If I type in exactly what you listed, I still only get the data fro
cell A1. If I try something like this
=INDIRECT("'[" & D1 &".CSV]" & D1 & "'!" & CELL("address",A1:C1))
It is still a valid formula but still only gives the contents of Cel
A1. Do the cells in the destination workbook have to be "pre-defined
for the number of cells to be used somehow or can the formula jus
appear in a cell and the data range will fill in below and to the righ
as necessary depending on what is liked from the source workbook
 
F

Frank Kabel

Hi
just use this formula:
=INDIRECT("'[" & D1 &".CSV]" & D1 & "'!" & CELL("address",A1))

and copy it to the other cells
 
B

bredar

When I copy the formula to an adjacent cell both the A1 reference and
the D1 reference gets changed by 1 row or column and I have to go bac
and correct the D1 reference since this must remain D1 in all cells.
Also this is not practical since the range of cells I will link wil
vary from day to day and it would take forever to get thousands o
cells prepared by doing this copy procedure. Maybe I just did the cop
wrong
 
B

bredar

Got it! Thank you for your help. I just added in the $ and didn't nee
to look at the link you gave me yet but I will when I have some time.
I placed the formula in the top left corner of where i wanted to star
and pasted it into the rest of the cells and it works great
 
Top