file path reference using existing spreadsheet cells

[shammer]

Hello All,

I am hitting a wall when I try to use cells in my active spreadsheet to refer to a filepath of external source.

I am trying to reach the following filepath:

=+('G:EdFinExpA MC[filename.xls]tab'!B6)

So, I would like to input in cells A2, B2, C2, ..., I2 - the individual components to reach the target cell. I apparently do not know the correct symbols to use to link them all together.

Basically, only two of the inputs (the filename and the cell change periodically).

Any help on this one would be greatly appreciated.

 

[nick m]

I am not entirly sure of what you are trying to do....but i think the best piece of advice passed onto me was to record a macro to do it then view the code and try to understand it and then apply it in future, And now i pass it on to you.

Hope it works

 

[shammer]

I am trying to find a way to create a filepath by referencing the individual components of that filepath in other cells in the spreadsheet.

Basically I would like the cells in row 5 to refer to cells A1 through H1 to construct a filepath.

For example, if I want cell A5 to reference the following filepath:

'C:AssetsOctoberFUND1[daily 1015.xls]1234'!$J14

I would like cell A1 to contain the C: drive reference; A2 to contain the Assets folder reference; ... and cell G1 to contain the J14 cell reference.

I want to know how to construct the formula in cell A5 to construct this filepath by only referring to the cells in row 1.

Any help would be greatly appreciated!

 

[lenze]

Something like

Book1
	A	B	C	D	E	F	G
1	C	Assets	October	FUND1	daily1015.xls	1234	J14
2
3
4
5	C:AssetsOctoberFUND1[daily1015.xls.xls]1234'!$J14
6
7	=INDIRECT("A1")&":"&INDIRECT("B1")&""&INDIRECT("C1")&""&INDIRECT("D1")&"["&INDIRECT("E1")&".xls]"&INDIRECT("F1")&"'!$"&INDIRECT("G1")
8
Sheet1

 

[shammer]

I have managed some success with this. I am able to get the text in cell A5 to spell out the filepath that I want, but I am unable to get the cell to pull the information that is contained in cell J14 within the other spreadsheet.

How do I get it to execute the filepath instructions?

 

[shammer]

any advice on this issue from anyone? thanks!

 

[just_jon]

In lenze's example, try adding this
"="&
just after the equal sign.