copy the content variable in the formula

[maikeyjohn]

	A	B
1	AAPL	MSFT
2	='C:\Documents and Settings\SASTC\My Documents\Stock Data\[AAPL.csv]AAPL'!E2	='C:\Documents and Settings\SASTC\My Documents\Stock Data\[MSFT.csv]MSFT'!E2

<tbody>
</tbody>

how should i COPY automatically "XYL" written as in formula (XYL.csv and XYL') comprehending with the top bar?

Tried "&C1&" but it`s not working.

 

[wigi]

What formula do you get, if you open the source CSV file, use a manual approach to get the cell value (*) and then close the source CSV file again.

(*) in the cell containing the formula, typ:

=

and point to the source cell and hit Enter.

 

[maikeyjohn]

='AAPL.csv'!E2
='MSFT.csv'!E2
OTHERWISE WITH MY XYL STOCK IT SHOWS FORMULA ITSELF
='C:\Documents and Settings\SASTC\My Documents\Stock Data\["&C1&".csv]"&C1&"'!E2

 

[wigi]

Can you wrap the INDIRECT function around the string above (and leave the source file opened)?

 

[maikeyjohn]

for the =INDIRECT(...) ERROR IS #REF!
for the =INDIRECT.EXT(....) ERROR IS #VALUE! ----> USING Morefunc Addin
both by keeping the source file open !!!

 

[wigi]

The INDIRECT function needs a text argument: did you use " " around the argument within the INDIRECT function ?

 

[maikeyjohn]

both tried as
=INDIRECT('C:\Documents and Settings\SASTC\My Documents\Stock Data\["&C1&".csv]"&C1&"'!$E2) and
=INDIRECT("'C:\Documents and Settings\SASTC\My Documents\Stock Data\["&C1&".csv]"&C1&"'!$E2")

 

[maikeyjohn]

actually it works with the latter one, thnx for reminding !!!!

merci !!!!

 

[wigi]

The second formula works for me, on a dummy Book1.csv file and worksheet Book1.
The source file Book1.csv is opened to make it work.

 

[wigi]

Indeed.

 

[maikeyjohn]

hhaha silly me !!!
thanks once again !!!!